Macro loop Copy paste in a column with diff¸erent range each time

[cbdunnigan]

Hi,

I have a long list of names that are separated by a different number of empty lines inbetween each other. I need to copy and paste until the next name.
Anyone can help me doing this loop copy=paste macro?

Example, this:

should become:

Thank you,
Colin

 

[Fluff]

What determines how many times you copy the last value in the list?

 

[cbdunnigan]

The number of empty cells between the 2 names, which is different from one name to the other

 

[Fluff]

What about the last name?

 

[cbdunnigan]

it can paste the name until the end of the workbook, i'll simply delete the unnecessary ones after

 

[Fluff]

That's over a million rows
Is there another column that dictates the last used row in the sheet?

 

[cbdunnigan]

yes, the last row is 26379
it is marked by this:
"Generated On: 11/19/2019" On column A

 

[cbdunnigan]

and there are 492 names

 

[Fluff]

Ok, try this

Sub cbdunnigan()
    With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
        .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
        .Value = .Value
    End With
End Sub

Change the two Bs in green to match the column you want this to work on.

 

[cbdunnigan]

THANKS!! Works perfectly and the macro is very quick to operate