Skip blank cells in macro

[seho22]

Hi,
My data like this:

some text
some text
some text
value1


-empty cell -


some text
some text
some text
value2

My expectations of the macro is:
1. start from down to up.
2. copy value2 to three cells and stop while on empty cell
3. copy value1 to three cells

Finally my data should be like this:

value1
value1
value1
value1


-empty cell -


value2
value2
value2
value2


Thanks and sorry for my bad english

 

[Rick Rothstein]

I think this macro will do what you want...

Sub CopyValuesUp()
  Dim Ar As Range
  On Error GoTo NoData
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    Ar(Ar.Count).Copy Ar
  Next
NoData:
End Sub

 

[seho22]

Hey Rick you are a hero! Thank you so much.