Solved: VBA : Autofill Rows Dynamically

[khaos]

Hello everyone.

I am copy/pasting data between worksheets & I'm running into the issue of blank rows when I transpose the data. I can use the Macro recorder to show the Autofill, but I want to do this dynamically. This is a sample of what I have so far:
Sub AutofillPaste()
' AutoFill Data in Blank Rows
Selection.Copy
Range("A2,A3,A4").Select
Range("A4").Activate
ActiveSheet.Paste
Range("A5").Select
Application.CutCopyMode = False
Selection.Copy
Range("A5,A6,A7,A8").Select
Range("A8").Activate
ActiveSheet.Paste
End Sub

Basically this code copies the cell value & pastes it into the next couple of blank cells under it manually. I want to do this dynamically to copy a value & paste it in the next blank cell until it reaches a different cell value, then have it copy the 2nd cell value & paste it into the blank cells in that particular column.

Can someone please help me make this dynamic?

Thanks.

[p45cal]

For Each cll In Range("A2:A8").Cells
If Len(cll.Value) = 0 Then cll.Offset(-1).Copy cll
Next cll
but be aware that if one of the cells hasa formula which returns a value "" (a zero length string) it will overwrite this cell. eg.
=if(S33 = 33, "Hello","")

[khaos]

Hey pascal, I tried your code & I get a compile error near "For each cell..." any ideas?

[Simon Lloyd]

p45cal, said "cll" not Cell, but you should declare your variables like this

Dim cll as Range

[khaos]

I tested it on a small file & this is the error that I get, I'm posting a snapshot of it. Perhaps I'm doing something wrong ?

Thanks

[Simon Lloyd]

Your problem is that you have entered it in the worksheet code module not a standard sub. p45cal's code works as is :)

[khaos]

Thanks guys this works perfectly. It does what I want it do. My only concern is that it takes time for the Macro to run since I'm specifying it over a number of columns. But it is exactly what I'm looking for.

Thanks a lot guys this helps me a ton.