Second Last Row in VB

[tharindudk]

Hi,

I have created a Print button and now i want to set a marco to it, so that it will only print the specific range.

The Range I want to Print is B2:M46, however M46 can change if new lines are inserted. From row 48 onwards I have hidden so my last visible row is row 48.

So for range to select i was thinking B2:Lastrow-2. But I am not sure how to select Last Row -2 and then assign print properties to it.

Any help is much appriciated.

Thankyou

[stnkynts]

Maybe this. I wasn't sure if you wanted to set up a Print Area so I added it. You could just as easily say myRange.PrintOut

Please Login or Register  to view this content.

[jmac1947]

hi tharindudk

when you say last row, do you mean the actual last row in the spreadsheet. This snippet will get you the last row of the nominated spreadsheet tab, you can then adjust the value

Please Login or Register  to view this content.

Jmac

[tharindudk]

Hi, This is exactly what I want to do, However I get an object defined error in line

Please Login or Register  to view this content.

Do you know why I would get that?

Thanks.

[stnkynts]

Nope. It works just fine for me on a test workbook. I would need to see you workbook to be able to tell you for sure. More than likely its an error on your side where you said column M but you really meant column L. Or you didn't have any data in column M when you tried the code.

[tharindudk]

Hi stnkynts,
Yes I don't have any Data in column M. Its just a border for the page.

Do I need to have data there?

Thanks

[Trebor76]

Hi tharindudk,

Try this where if there's no data the 'lngMyRow' variable will be set to 2 (change to suit):

Please Login or Register  to view this content.

HTH

Robert

[tharindudk]

Hi, now the range comes up as B2:M38.

[Trebor76]

So the last row from columns B to M (inclusive) must be 40

[tharindudk]

The Range I want to Print is B2:M46, however M46 can change if new lines are inserted. From row 48 onwards I have hidden so my last visible row is row 48.

[Trebor76]

Regardless if the row is hidden or not the last row to have data across columns B to M is 40.

Try this where the 'lngMyRow' variable is based on the first hidden row (if there is one):

Please Login or Register  to view this content.

HTH

Robert