I'm working with a huge spreadsheet of over 20,000 rows. I need a macro that
will copy the number in a cell down to the first cell with another number.
In other words, cell A35 has, for example, 4500245842 in it. A42 has
450245989 in it. Then A85 has 450246910 in it. The macro needs to complete
A36-A41 with 4500245842, A43-A84 with 450245989, and so on down the 20,000+
rows. Is this possible? Connie

Sub copy_down()
Dim r As Range, rr As Range, n As Long
With ActiveSheet.UsedRange
n = .Rows.Count + .Row - 1
End With

Set r = Range(Cells(1, "A"), Cells(n, "A")).SpecialCells(xlCellTypeBlanks)

For Each rr In r
rr.FillDown
Next
End Sub

--
Gary''s Student - gsnu200786

No need for a macro

Highlight the area that you want to fill in blanks including the first
non-blank cell.

press F5 (goto)

Click the button called special

Click blanks hit okay

if the first blank cell enter =(cell above)

and then hold the control key down while hitting enter

this will fill in the blanks......
--
Wag more, bark less


"Connie Martin" wrote:

I'm working with a huge spreadsheet of over 20,000 rows. I need a macro that
will copy the number in a cell down to the first cell with another number.
In other words, cell A35 has, for example, 4500245842 in it. A42 has
450245989 in it. Then A85 has 450246910 in it. The macro needs to complete
A36-A41 with 4500245842, A43-A84 with 450245989, and so on down the 20,000+
rows. Is this possible? Connie

Dear Gary"s student,
You are amazing! Thank you so very much. Do you know how much work you
saved me? I can't begin to tell you. Thank you! Connie

"Gary''s Student" wrote:

Sub copy_down()
Dim r As Range, rr As Range, n As Long
With ActiveSheet.UsedRange
n = .Rows.Count + .Row - 1
End With

Set r = Range(Cells(1, "A"), Cells(n, "A")).SpecialCells(xlCellTypeBlanks)

For Each rr In r
rr.FillDown
Next
End Sub

--
Gary''s Student - gsnu200786

Brad, I will try this sometime when I have a smaller worksheet. With over
20,000 rows, the macro works much more quickly. I have printed yours to keep
for future reference. Thank you. Connie

"Brad" wrote:


No need for a macro

Highlight the area that you want to fill in blanks including the first
non-blank cell.

press F5 (goto)

Click the button called special

Click blanks hit okay

if the first blank cell enter =(cell above)

and then hold the control key down while hitting enter

this will fill in the blanks......
--
Wag more, bark less


"Connie Martin" wrote:

I'm working with a huge spreadsheet of over 20,000 rows. I need a macro that
will copy the number in a cell down to the first cell with another number.
In other words, cell A35 has, for example, 4500245842 in it. A42 has
450245989 in it. Then A85 has 450246910 in it. The macro needs to complete
A36-A41 with 4500245842, A43-A84 with 450245989, and so on down the 20,000+
rows. Is this possible? Connie

Brad, I just tried yours in another column. It works like a charm, too!
Thank you so much. This is simple and great to know. Thanks! Connie

"Brad" wrote:


No need for a macro

Highlight the area that you want to fill in blanks including the first
non-blank cell.

press F5 (goto)

Click the button called special

Click blanks hit okay

if the first blank cell enter =(cell above)

and then hold the control key down while hitting enter

this will fill in the blanks......
--
Wag more, bark less


"Connie Martin" wrote:

I'm working with a huge spreadsheet of over 20,000 rows. I need a macro that
will copy the number in a cell down to the first cell with another number.
In other words, cell A35 has, for example, 4500245842 in it. A42 has
450245989 in it. Then A85 has 450246910 in it. The macro needs to complete
A36-A41 with 4500245842, A43-A84 with 450245989, and so on down the 20,000+
rows. Is this possible? Connie