Skipping empty rows when copying a formula down a column

  • Thread starter Thread starter Rachel Jones
  • Start date Start date
R

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

Rachel said:
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
Click to expand...
 
Back
Top