Dim rng As Range
With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(rows.count, 1).End(xlUp))
End With
With Worksheets("Sheet2")
..Range(rng.Address).FormulaR1C1 = "=Sheet1!RC"
End With

or if you want to delete the rows

Dim rng As Range
With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(rows.count, 1).End(xlUp))
rng.specialcells(xlblanks).Entirerow.Delete
Set rng = .Range(.Cells(2, 1), .Cells(rows.count, 1).End(xlUp))
End With
With Worksheets("Sheet2")
..Range(rng.Address).FormulaR1C1 = "=Sheet1!RC"
End With

--
Regards,
Tom Ogilvy


"Sandeman" wrote:


I am trying to run the following Macro and I'm not having any trouble
except every 4th row in the dataset is blank, causing this macro to
stop dead in its tracks. What can I do to add "filler" data in every
4th row for the length of the dataset or perhaps delete every 4th row
and move the data up?

Thanks particularly to Tom O. who has been a great help this week.

Dim rng As Range
With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
With Worksheets("Sheet2")
.Range(rng.Address).FormulaR1C1 = "=Sheet1!RC"
End With


--
Sandeman
------------------------------------------------------------------------
Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440
View this thread: http://www.excelforum.com/showthread...hreadid=523586