Excel AutoFill

[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

 

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

 

[Andrew Lavery]

Hi Tom,

Thanks for the swift reply. I have tried your suggestion, but nothing
seems to happen??? I am also wondering if I haven't explained the
scenario very well. I have put a screenshot online to help anyone that
has any suggestions.

Screenshots: http://www.slc.auckland.ac.nz/excelexample/

Regards,
Andrew

 

[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

 

[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