Every 4th cell is blank and stops my macro!

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

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


--
Don Guillett
SalesAid Software
[email protected]
"Sandeman" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

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