**macros**

[f_pamela]

I need to know if there is any way that some empty spaces copy the text above
it, the problem is that the spaces change from 2 to 8, like this
085800 47
54
085805 47
54
57
58
086335 47
and i need to copy the text above it something like..
085800 47
085800 54
085805 47
085805 54
085805 57
085805 58
086335 47
I really appreciate your time to answer my question and please if there is
any way let me know, because the spreadsheet if too long to do one by one

 

[Gary''s Student]

Try this small macro:

Sub copy_down()
Dim r As Range, rr As Range, n As Long
With ActiveSheet.UsedRange
n = .Rows.Count + .Row - 1
End With

Set r = Range(Cells(1, "A"), Cells(n, "A")).SpecialCells(xlCellTypeBlanks)

For Each rr In r
rr.FillDown
Next
End Sub

 

[Jim Cone]

John Walkenbach shows a way here (with pictures)...
http://j-walk.com/ss/excel/usertips/tip040.htm
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"f_pamela"
wrote in message
I need to know if there is any way that some empty spaces copy the text above
it, the problem is that the spaces change from 2 to 8, like this
085800 47
54
085805 47
54
57
58
086335 47
and i need to copy the text above it something like..
085800 47
085800 54
085805 47
085805 54
085805 57
085805 58
086335 47
I really appreciate your time to answer my question and please if there is
any way let me know, because the spreadsheet if too long to do one by one

 

[Emma]

Hi,

You can also use a formula to get the data as you want (I assume your data
is in a pivot table?)

Assuming the data you have is in Column A and B (with the first piece of
data at row 2) then you could use the following formulas:
In column C use the formula
=if(isblank(A2),C1,A2)
In column D use the formula
=if(isblank(B2),D1,B2)

Then you just need to copy to the end of your data table.
I use this formula all the time with the data I get from pivot tables.

Goodluck with which ever method you use.

 

[f_pamela]

Thank you very much, have a great day!

Try this small macro:

Sub copy_down()
Dim r As Range, rr As Range, n As Long
With ActiveSheet.UsedRange
n = .Rows.Count + .Row - 1
End With

Set r = Range(Cells(1, "A"), Cells(n, "A")).SpecialCells(xlCellTypeBlanks)

For Each rr In r
rr.FillDown
Next
End Sub

 

[Gord Dibben]

You don't need a macro.

Select column A or whichever column............

F5>Special>Blanks>OK

Type an = sign in the active cell and point to cell above.

Hit CTRL + Enter to fill.


Gord Dibben MS Excel MVP