Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel - rows repeat on top | Excel Worksheet Functions | |||
How do I repeat rows at the top of SOME of the pages in Excel | Excel Worksheet Functions | |||
Why can the number of iterations repeat many times as a loop?? | Excel Discussion (Misc queries) | |||
how to repeat rows at the bottom while using the rows repeat at to | Setting up and Configuration of Excel | |||
Title Cut Off if Rows to Repeat & Columns to Repeat are Both Selec | Excel Discussion (Misc queries) |