Copy down to next Cell

[samcannet1]

I have a spreadsheet that is 30000 lines long. I have to copy down a
different value to about 20 spaces below.
For example I have the first value in b2, the the next 20 rows under
this are blank then the new value starts in b22 then the next 20 rows
under this are blank the then new value starts in B42.

How to I fill in the blanks all the way to the end of the 30000 lines?

Thanks
Sam

 

[Guest]

try this macro:

Sub samc()
n = Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To n
If (IsEmpty(Cells(i, "B"))) Then
Cells(i, "B").FillDown
End If
Next
End Sub

 

[MartinW]

Hi Sam,

If I understand you correctly,

Insert a helper column C
Put this in C2
=IF(B2>0,B2,C1)
and drag down to the end of your data.

Then select column C do a copy>paste special>values
on it and then delete column B.

HTH
Martin

 

[Gord Dibben]

Select column B2 then SHIFT + End + Downarrow then F5>Special>Blanks>OK

Enter an = sign in active cell which should be B3

Point or arrow up to B2 and CRTL + ENTER

Copy then paste special>values>OK>Esc


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Make that "select B2 then.........."


Gord

Select column B2 then SHIFT + End + Downarrow then F5>Special>Blanks>OK

Enter an = sign in active cell which should be B3

Point or arrow up to B2 and CRTL + ENTER

Copy then paste special>values>OK>Esc


Gord Dibben MS Excel MVP

 

[Guest]

Hi,

Do the following:
1. Enter a value in cell B2. (for example, enter 22)
2. Select a range from B2:B20.
3. Press Ctrl+D.
4. Repeat the Step 1 to Step 3 after entering a new value in cell 23.

Challa Prabhu