AutoFill down to the first non empty cell in a macro

[ECKSELL]

Hi, </SPAN>
I have a problem with AutoFill in a macro where I want to AutoFill down to the first non empty cell – and my problem is that I always Autofill down with a specific predefined range – lets say 10 rows.. and sometimes it is 10, 12, 30, etc.</SPAN>

The case:</SPAN>
Column B have a lot of different text content in row 1 to 1000</SPAN>

Column A have values </SPAN>
‘Fox’ in cell A15 </SPAN>
‘Zebra’ in cell A25</SPAN>
‘Dog’ on cell A31</SPAN>
‘Cat’ in cell A39</SPAN>

All other cells in Column A are empty.</SPAN>

What I want to do is to have a macro which starts from the current market cell (for example A15) and then go down to the first ‘non-empty’ Cell A25 – and then copy the content (‘Zebra’) taken from from A25 – and then go up to the first non empty row (A15 just below ‘Fox’) – and go down 1 step (to the empty row A16) – and then ‘Paste’ ‘Zebra’. Then I want to Autofill ‘Zebra’ down to the first non empty row A25. This means paste ‘Zebra ’ 1 time – and then AutoFill ‘Zebra’ 9 times down between A15 and A25. The macro should end with having the cell marked a the last ‘non-empty’ row with the copied values (‘Zebra’ in cell A25)</SPAN>

The dilemma here is that the distance between the cells in Column A are different – sometimes 10 (between A15 to A25 but the next difference between ‘Zebra’ and ‘Dog’ is just 6 (between A25 and A31). The problem (I think) is that the code have A1:A9 below – and when I execute the macro manually the 2^nd</SPAN> time next time – it copies the value ‘Dog’ 1+9 times down to A34 and not just down to the first ‘non-empty’ cell A31 </SPAN>

The macro I have so far is:</SPAN>

Sub CopyCellswithAutoFill()</SPAN>
'</SPAN>
Selection.End(xlDown).Select</SPAN>
Selection.Copy</SPAN>
Selection.End(xlUp).Select</SPAN>
ActiveCell.Offset(1, 0).Range("A1").Select</SPAN>
ActiveSheet.Paste</SPAN>
Application.CutCopyMode = False</SPAN>
Selection.AutoFill Destination:=ActiveCell.Range("A1:A9")</SPAN>
ActiveCell.Range("A1:A9").Select</SPAN>
Selection.End(xlDown).Select</SPAN>
End Sub</SPAN>


Thanks, </SPAN>

 

[VoG]

Try

Sub atest()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("A1:A" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C"
    .Value = .Value
End With
End Sub

 

[ECKSELL]

Thanks for the quick reply MrExcel MVP!</SPAN>
The macro you sent is a good one and I can use that as well and all the cells gets populated nicely!</SPAN>
However – I would like to just execute this once – and then stop. So I want to start from where the cell is active – and then go down to the next non-empty row A25 – and copy – then go up to A15 – then down one to A16 – and then Auto fill down to A24 – and then step down 1 step to A25. Then I want to stop. How is this done! What you sent was another very useful macro – but I want to execute it manually and just do one fill – not all in the columns.</SPAN>

Thanks again,</SPAN>