Need a macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Connie Martin - 15 May 2008 17:08 GMT

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

Reply to this Message

Gary''s Student - 15 May 2008 17:14 GMT

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

Signature

Gary''s Student - gsnu200786

Reply to this Message

Connie Martin - 15 May 2008 19:37 GMT

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

> Sub copy_down()
> Dim r As Range, rr As Range, n As Long
[quoted text clipped - 8 lines]
> Next
> End Sub

Reply to this Message

Brad - 15 May 2008 18:51 GMT

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......

Signature

Wag more, bark less

> 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

Reply to this Message

Connie Martin - 15 May 2008 19:41 GMT

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

> No need for a macro
>
[quoted text clipped - 19 lines]
> > A36-A41 with 4500245842, A43-A84 with 450245989, and so on down the 20,000+
> > rows. Is this possible? Connie

Reply to this Message

Connie Martin - 15 May 2008 19:57 GMT

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

> No need for a macro
>
[quoted text clipped - 19 lines]
> > A36-A41 with 4500245842, A43-A84 with 450245989, and so on down the 20,000+
> > rows. Is this possible? Connie

Reply to this Message