Excel AutoFill

A

Andrew

Hi,

I'm new to VB programming but I'm sure that what I want to do is quite
simple. I have a 2 spreadsheets with 30,000 rows each. Every 12th row
has a number in it which I would like to put into the above 11 cells
automatically. I can use AutoFill for this, but this will result in
over 5,0000 operations. Can anyone give me any pointers as to how I
would go about writing a macro for this?

My initial guess is that I will need to use a FOR loop/AutoFill
feature but thats about it.

Any help would be appreciated.

Andrew
 
T

Tom Ogilvy

Assume the cells are blank
first blank cell is A1, column to work on is column A

Dim rng as Range, ar as Range
set rng = Columns(1).Specialcells(xlblanks)
for each ar in rng.Areas
ar.value = ar.offset(1,0)(1).Value
Next


Test on a copy of your workbook.
 
N

Norman Jones

Hi Andrew,

Amending, to operate on Column B , and correcting a minor typo in the
offset, Tom's code works for me and reads:

Dim rng As Range, ar As Range

Set rng = Columns(2).SpecialCells(xlBlanks)
For Each ar In rng.Areas
ar.Value = ar.Offset(1, 0)(11).Value
Next
 
T

Tom Ogilvy

Actually, as intended it should have been

Sub AAA()
Set rng = Columns(2).SpecialCells(xlBlanks)
For Each ar In rng.Areas
ar.Value = ar.Offset(11, 0)(1).Value
Next

End Sub

But I like your idea better, so it could be:

Sub AAB()
Set rng = Columns(2).SpecialCells(xlBlanks)
For Each ar In rng.Areas
ar.Value = ar(12).Value
Next
 
Top