MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Looping through rows of data


Posted by MattH on December 18, 2001 6:39 AM

Can anyone suggest why this stops at EndRow = Range("A65536").End(xlUp).Select and just sits there with Cell A169 selected and not carry on through the rows? I'd like to know - it's annoying me.

Sub PrintForm()

'Step through rows of data one at a time

Dim EndRow, counter As Integer
Sheets("Data").Select
EndRow = Range("A65536").End(xlUp).Select

For counter = 1 To EndRow
Sheets("Data").Cells(counter, 1).Select
Selection.copy
Range("NAME").Select
Selection.Paste

Sheets("Data").Cells(counter, 4).Select
Selection.copy
Range("NI").Select
Selection.Paste

Sheets("Data").Cells(counter, 8).Select
Selection.copy
Range("CODE").Select
Selection.Paste

Sheets("Data").Cells(counter, 11).Select
Selection.copy
Range("ACCOUNT").Select
Selection.Paste

ActiveWindow.SelectedSheets.PrintOut Copies:=15
Next counter

End Sub

Thanks

Matt


Posted by Paul Akkermans on December 18, 2001 6:51 AM

Change
EndRow = Range("A65536").End(xlUp).Select
in
EndRow = Range("A65536").End(xlUp).Row

Paul

Posted by Russell Hauf on December 18, 2001 7:04 AM

Change the .Select in your xlUp line to .Row

You will probably also need to change

Selection.Paste to

ActiveSheet.Paste

Hope this helps,

Russell

(the .Select statement just evaluates to True, so EndRow = 1 and the loop is never entered).

Posted by MattH on December 18, 2001 7:16 AM

OK. So I've changed it, and hey... it works. Xcept now it stops at Sheets("Data").Cells(counter, 4).Select with error 1004. DO I have to re-initialise the counter - can't see why? Sorry if this seems dumb - I've not done a huge amount of Excel VBA - but it is increasing.

Sub PrintForm()

'Step through rows of data one at a time

Dim EndRow, counter As Integer
Sheets("Data").Select
EndRow = Range("A65536").End(xlUp).Row

For counter = 1 To EndRow

Sheets("Data").Cells(counter, 1).Select
Selection.copy
Application.Goto Reference:="NAME"
ActiveSheet.Paste

Sheets("Data").Cells(counter, 4).Select
Selection.copy
Application.Goto Reference:="NI"
ActiveSheet.Paste

Sheets("Data").Cells(counter, 8).Select
Selection.copy
Application.Goto Reference:="CODE"
ActiveSheet.Paste

Sheets("Data").Cells(counter, 11).Select
Selection.copy
Application.Goto Reference:="ACCOUNT"
Selection.PasteSpecial Paste:=xlValues

ActiveWindow.SelectedSheets.PrintOut Copies:=1

MsgBox ("Continue")

Next counter

End Sub


Posted by Bariloche on December 18, 2001 5:56 PM

Matt,

Not sure exactly why it would stop there. Are you sure you have a range named "NI"?

I copied your code and ran through it with no problem.

Incidentally, I changed your code somewhat:

Sub PrintForm()

'Step through rows of data one at a time

Dim EndRow As Integer, counter As Integer

Sheets("Data").Select
EndRow = Range("A65536").End(xlUp).Row

For counter = 1 To EndRow

Sheets("Data").Cells(counter, 1).Copy Destination:="NAME"

Sheets("Data").Cells(counter, 4).Copy Destination:="NI"

Sheets("Data").Cells(counter, 8).Copy Destination:="CODE"

Sheets("Data").Cells(counter, 11).Copy
Range("ACCOUNT").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = xlCopy

ActiveWindow.SelectedSheets.PrintOut Copies:=1

MsgBox ("Continue")

Next counter

End Sub

1) As you have it, EndRow is not dimensioned as Integer. The change I show corrects that oversight.

2) Your code will run faster if you don't have Excel "select" stuff it doesn't have to. With a proper reference you can get to the same end, faster. Go through the code posted above and I think you'll see what i mean.


have fun

Sheets("Data").Cells(counter, 4).Select Application.Goto Reference:="NI" Application.Goto Reference:="CODE" Sheets("Data").Cells(counter, 11).Select Application.Goto Reference:="ACCOUNT" Selection.PasteSpecial Paste:=xlValues MsgBox ("Continue")