Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,272
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
excel - rows repeat on top cxxper Excel Worksheet Functions 4 March 1st 10 09:26 AM
How do I repeat rows at the top of SOME of the pages in Excel IM333 Excel Worksheet Functions 2 September 10th 09 09:24 PM
Why can the number of iterations repeat many times as a loop?? Jorge Luis Excel Discussion (Misc queries) 0 February 1st 08 05:15 PM
how to repeat rows at the bottom while using the rows repeat at to Rows to repeat at the bottom Setting up and Configuration of Excel 1 August 31st 05 02:06 PM
Title Cut Off if Rows to Repeat & Columns to Repeat are Both Selec unibaby Excel Discussion (Misc queries) 2 August 24th 05 04:29 PM


All times are GMT +1. The time now is 12:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"