Every 4th cell is blank and stops my macro!

[Sandeman]

I am trying to run the following Macro and I'm not having any troubl
except every 4th row in the dataset is blank, causing this macro t
stop dead in its tracks. What can I do to add "filler" data in ever
4th row for the length of the dataset or perhaps delete every 4th ro
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 Wit

 

[Don Guillett]

try

Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))

Set rng = .Range(.Cells(2, 1), .Cells(rows.count, 1).End(xlup))

 

[Ardus Petus]

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

HTH
--
AP

"Sandeman" <[email protected]> a écrit
dans le message de
news:[email protected]...

 

[Sandeman]

Nevermind. I think I figured this out. Google is your friend

 

[Tom Ogilvy]

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