Is there a way to find the last column in a worksheet?

[zombiemaster]

I know about endRow$ = ActiveCell.Row but is there a similar way to find the last column the same way? I tried versions of EndColumn$ = ActiveCell.Column but it didn't seem to work...

What I'm trying to do is set the print area for the entire range of data on a worksheet but the number of rows and columns can change from day to day. When I run the report each day, I have to go in and manually drag the print area over to encompass all the columns.

Thanks for any help!

~ZM~

 

[VoG]

Perhaps

LastCol = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

 

[zombiemaster]

Thanks VoG - I'm not sure I understand what it's doing so I need to ask for clarification (as usual!)...here is the code I used today since I knew my last column was going to be "AA". Starting in cell A4, how would I incorporate your code into this so my print range would find the end row and the end column automatically?

ActiveSheet.PageSetup.PrintArea = ("$A$4:$AA" + endRow$)

~ZM~

 

[VoG]

Try

Sub test()
Dim LastRow As Long, LastCol As Long
With ActiveSheet
    LastRow = .Columns(1).Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    LastCol = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    .PageSetup.PrintArea = .Range(.Cells(4, 1), .Cells(LastRow, LastCol)).Address
End With
End Sub

 

[zombiemaster]

Thanks again VoG - I'm going to give that a try later today and will let you know how it works!


~ZM~

 

[zombiemaster]

That worked perfectly, VoG - I was able to incorporate that code in a few places I needed it throughout the entire script for multiple sheets.

Thank you very much - you ROCK!


~ZM~

 

[blaow]

What I always use:

lastrow = record.UsedRange.Rows(record.UsedRange.Rows.Count).row

Where record is the sheet's name property