Excel VBA - Repeat for loop until blank rows
 

Hello
Is there a way that I can have a loop repeat as many times as there i
data or until it reaches the first row that is blank. I would like t
know so that when I add or delete rows I do not have to modify th
macro as well.
Any thoughts???

I'm trying to apply it to the following code

Sub Actuals()
'
' Actuals Macro
' Macro recorded 6/29/2004 by Robert Belforti
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Dim j As Long, i As Long
j = 2
For i = 4 To 248 Step 4
Sheets("sheet2").Cells(i, 2).Resize(1, 12).Copy
Sheets("Sheet1").Cells(j, 4).PasteSpecial Paste:=xlAll
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
j = j + 12
Next

End Sub

Would another approach be to get a variable to count the number of row
in the spreadsheet and assign to a variable? So in line 3 of the macr
listed above instead of writing
For i = 4 To 248 Step 4

Could I write :
dim RowCount = Sheets("sheet2").CountRows ( );
For i = 4 To RowCount Step 4


Any thoughts?
Thanks
Bob Belfort

--
Message posted from http://www.ExcelForum.com

Excel VBA - Repeat for loop until blank rows
 

For al non-blank rows

cRows = Cells(Rows.Count,"A").End(xlUp).Row
For i =1 To cRows
.... do your stuff
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rbelforti " wrote in message
...
Hello
Is there a way that I can have a loop repeat as many times as there is
data or until it reaches the first row that is blank. I would like to
know so that when I add or delete rows I do not have to modify the
macro as well.
Any thoughts???

I'm trying to apply it to the following code

Sub Actuals()
'
' Actuals Macro
' Macro recorded 6/29/2004 by Robert Belforti
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Dim j As Long, i As Long
j = 2
For i = 4 To 248 Step 4
Sheets("sheet2").Cells(i, 2).Resize(1, 12).Copy
Sheets("Sheet1").Cells(j, 4).PasteSpecial Paste:=xlAll,
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
j = j + 12
Next

End Sub

Would another approach be to get a variable to count the number of rows
in the spreadsheet and assign to a variable? So in line 3 of the macro
listed above instead of writing
For i = 4 To 248 Step 4

Could I write :
dim RowCount = Sheets("sheet2").CountRows ( );
For i = 4 To RowCount Step 4


Any thoughts?
Thanks
Bob Belforti


---
Message posted from http://www.ExcelForum.com/