Every 4th cell is blank and stops my macro!
 

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

--
Sandema
-----------------------------------------------------------------------
Sandeman's Profile: http://www.excelforum.com/member.php...fo&userid=3244
View this thread: http://www.excelforum.com/showthread.php?threadid=52358

Every 4th cell is blank and stops my macro!
 

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

"Sandeman" wrote in
message ...

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

Every 4th cell is blank and stops my macro!
 

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" a écrit
dans le message de
...

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

Every 4th cell is blank and stops my macro!
 

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

--
Sandema
-----------------------------------------------------------------------
Sandeman's Profile: http://www.excelforum.com/member.php...fo&userid=3244
View this thread: http://www.excelforum.com/showthread.php?threadid=52358

Every 4th cell is blank and stops my macro!
 

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