Skipping empty rows when copying a formula down a column

[Rachel Jones]

I am trying to write a macro which will enter a formula into a cell on
the fourth row of a spreadsheet, then copy it down the column -
allowing for the fact that there may be a groups of 2 empty rows at
periods.
My VBA knowledge is poor - I have got the formula copying OK (until it
hits the first empty row) like this:
Range("L4").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Set rng = Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0,
-1).End(xlDown))
Set rng = rng.Offset(0, 1)
rng.Formula = ActiveCell.Formula

but am unsure how to proceed from here.
Any help would be appreciated

Rachel

 

[Dave Peterson]

When there are gaps in the data so you don't want to use xldown, maybe xlup will
work:

dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
range("L4:L" & lastrow).formular1c1 _
= "=RC[-2]+RC[-1]"
end with

I used column A to determine the lastrow. Can you pick a column that always has
data?

If not, maybe you could use something like:
lastcell = .cells.specialcells(xlcelltypelastcell).row

But this often returns a number much higher than you would have guessed.

I am trying to write a macro which will enter a formula into a cell on
the fourth row of a spreadsheet, then copy it down the column -
allowing for the fact that there may be a groups of 2 empty rows at
periods.
My VBA knowledge is poor - I have got the formula copying OK (until it
hits the first empty row) like this:
Range("L4").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Set rng = Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0,
-1).End(xlDown))
Set rng = rng.Offset(0, 1)
rng.Formula = ActiveCell.Formula

but am unsure how to proceed from here.
Any help would be appreciated

Rachel