A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Excel VBA - Repeat for loop until blank rows



 
 
Thread Tools Display Modes
  #1  
Old July 7th 04, 09:13 PM posted to microsoft.public.excel.programming
rbelforti[_4_]
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

Ads
  #2  
Old July 7th 04, 10:48 PM posted to microsoft.public.excel.programming
Bob Phillips[_6_]
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/
>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
excel - rows repeat on top cxxper Excel Worksheet Functions 4 March 1st 10 08: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 04: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 01:35 AM.


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