How to reset variables in a loop

D

Dennis

Using XL 2003 & 97

The MyLastRow and MylastColumn variables are not resetting for each worksheet.

My goal is to set the print range of each worksheet to the used range.

What Am I doing wrong?

What is happening. is that every sheet's print range is set to the UsedRange
of the first sheet.

Sub Test()
Dim EachSheet As Worksheet
Dim myLastRow As Long, myLastCol As Long
For Each EachSheet In ActiveWorkbook.Worksheets
With EachSheet.PageSetup
myLastRow = Cells(Rows.Count, 1).End(xlUp).Row
myLastCol = Cells.SpecialCells(xlLastCell).Column
.PrintArea = ""
.PrintArea = Range(Cells(1, 1), Cells(myLastRow, myLastCol)).Address
.......................
End With

Next EachSheet

End Sub
 
K

kkknie

One small mistake. Since you are not selecting a new worksheet (jus
looping through them), you need to qualify which sheet to caluculat
lastrow and lastcol. Your original code was running with the firs
sheet as the active sheet so Cells(Rows.Count,1).End(xlUp).Row wa
performing the calc on that sheet. If you qualify the sheet usin
EachSheet.Cells(etc.) all should be fine.


Sub Test()
Dim EachSheet As Worksheet
Dim myLastRow As Long, myLastCol As Long
For Each EachSheet In ActiveWorkbook.Worksheets
With EachSheet.PageSetup
myLastRow = EachSheet.Cells(Rows.Count, 1).End(xlUp).Row
myLastCol = EachSheet.Cells.SpecialCells(xlLastCell).Column
.PrintArea = ""
.PrintArea = Range(Cells(1, 1), Cells(myLastRow, myLastCol)).Address
.......................
End With

Next EachSheet

End Sub
 
D

Dennis

Thank you very much for your time.

My variables are still not resetting after even after adding
EachSheet.Cells (etc.)

In fact, .PrintArea = "" is not clearing the previous PrintArea setting.
(The worksheets retain their previous PrintArea settings)

It is probably something very simple that I am missing.

Any other thoughts?

TIA Dennis
 
K

kkknie

I pasted you code and tried it and it worked fine for me.

One thing I noticed is that you are setting the last row as the las
value in column A. That's fine if A is always populated, so not a bi
deal. If it is, you should use the same way you set the max colum
(substituting Row for Column). Probably not an issue.

A way to troubleshoot what is going on is to put some debug.prin
statments in the code and check the immediate window for what i
happening. Something like:

Sub Test()
Dim EachSheet As Worksheet
Dim myLastRow As Long, myLastCol As Long
For Each EachSheet In ActiveWorkbook.Worksheets
With EachSheet.PageSetup
myLastRow = EachSheet.Cells.SpecialCells(xlLastCell).Row
myLastCol = EachSheet.Cells.SpecialCells(xlLastCell).Column
.PrintArea = ""
.PrintArea = Range(Cells(1, 1), Cells(myLastRow, myLastCol)).Address
Debug.Print EachSheet.Name & " Row: " & myLastRow & " Col: "
myLastCol
'....
End With

Next EachSheet

End Sub

My output from this was:

Sheet1 Row: 7 Col: 8
Sheet2 Row: 17 Col: 6
Sheet3 Row: 23 Col: 9

Let me know what you find out.
 
D

Dennis

You are absolutely correct. It was a Column A issue.

What is the safest way to re-code:
..PrintArea = Range(Cells(1, 1), Cells(myLastRow, myLastCol)).Address
to get all cells from A1 through Last cell?

Thanks very much!

Dennis
 
K

kkknie

Your code is fine, you just need to get the last row the same way yo
got the last column.

Old Way:

myLastRow = EachSheet.Cells(Rows.Count, 1).End(xlUp).Row

Better Way:

myLastRow = EachSheet.Cells.SpecialCells(xlLastCell).Row

You already did it this way for columns, so I'm not really telling yo
anything you didn't already know.

Good luck,
 
D

Dave Peterson

See one more reply at your other post.
Using XL 2003 & 97

The MyLastRow and MylastColumn variables are not resetting for each worksheet.

My goal is to set the print range of each worksheet to the used range.

What Am I doing wrong?

What is happening. is that every sheet's print range is set to the UsedRange
of the first sheet.

Sub Test()
Dim EachSheet As Worksheet
Dim myLastRow As Long, myLastCol As Long
For Each EachSheet In ActiveWorkbook.Worksheets
With EachSheet.PageSetup
myLastRow = Cells(Rows.Count, 1).End(xlUp).Row
myLastCol = Cells.SpecialCells(xlLastCell).Column
.PrintArea = ""
.PrintArea = Range(Cells(1, 1), Cells(myLastRow, myLastCol)).Address
.......................
End With

Next EachSheet

End Sub
Click to expand...
 
D

Dave Peterson

I'd use this style:

With EachSheet
myLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
myLastCol = .Cells.SpecialCells(xlLastCell).Column
.PageSetup.PrintArea _
= .Range(.Cells(1, 1), .Cells(myLastRow, myLastCol)).Address
end with

Note the dots in front of .range and .cells. This means that that thing refers
back to the previous With object (EachSheet in this case).

But take a look at your other thread for an alternative.
 
Top