Cells.SpecialCells(xlCellTypeLastCell).Row Question

[Wee Kuang]

Dear all,

I'm currently devising this code to do a automatic setting of print area. The statement goes like this.

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
ActiveSheet.PageSetup.PrintArea = "$A$1:$X$" & LastRow + 3

I thought it was alright but when I run the code and check the print area. I had like 7 more pages without any data inside these pages. Can anyone point to me if my code is correct?

Any help will more warmly welcome.

 

[GTO]

I believe SpecialCells uses the UsedRange, which can falsely report. In your code, tack in:

MsgBox Cells.SpecialCells(xlCellTypeLastCell).Address

Does it tell you an address that you expect, or way off down the sheet?

 

[Wee Kuang]

Hi GTO. Thanks for this little trick. It showed me X1050 which was totally off. Haha. The last cell should be X150 though.

The active sheet, that the code is working on for the print area, has its data content obtained from filtering, copy and paste(visible cells only) of another Masterlist.

Could this be the problem? Thanks for your help once again.

 

[GTO]

If I am understanding correctly, you are saying that you copy from a filtered list, and paste onto the sheet we're printing, right?

It shouldn't interfere, but try something easy. Whichever column can be counted on to have data in each row, select a cell. With the CTRL key pressed, press the down arrow key until it jumps to the end of the sheet (downward). Then press the up arrow once (CTRL is still depressed). Does that get you to the last row of visble data?

 

[Wee Kuang]

Hello. I tried what you suggested and it got me back to the last row of data which is row 150.

 

[GTO]

Okay, try:

ActiveSheet.PageSetup.PrintArea = "$A$1:$X$" & Cells(Rows.Count, "A").End(xlUp).Row + 3

I am guessing that column A is dependable to find the last row of data...

 

[Wee Kuang]

Hello. I tried what you suggested and it got me back to the last row of data which is row 150.

 

[Wee Kuang]

Hahaha. You are right! It got me what I wanted .

Thanks for your help!

 

[GTO]

You are most welcome and thank you for letting me know it worked