Active worksheet as variable broken

spencer_time

New Member
Hello guys, I need help again. I have a snippit that works when the sheet is defined (sheet3) but doesn't work when I try to make a variable for the active sheet (ws). I get an error that says "object doesn't support this property or method" and when I press debug it highlights my variable ws.

The following is the WORKING code with the sheet hardcoded in:
Code:
Sub count()

Dim lastRow As Long
lastRow = Sheet3.Cells(Rows.count, 1).End(xlUp).Row
MsgBox lastRow

Dim lastColumn As Long
lastColumn = Sheet3.Cells(1, Columns.count).End(xlToLeft).Column
MsgBox lastColumn

With Sheet3.UsedRange
    MsgBox lastRow & " rows and " & lastColumn & " columns"
    MsgBox "Sum of number of rows and number of columns = " & (lastRow + lastColumn)
End With

End Sub
The following is the NON-WORKING code with me trying to use a variable for the active sheet:
Code:
Sub count_tst()
    Dim wb As Workbook ' test dim
    Dim ws As Worksheet ' test dim
    Set wb = Application.ActiveWorkbook ' test
    Set ws = Application.ActiveWorksheet ' test
    Dim lastRow As Long
    Dim lastColumn As Long
    
lastRow = ws.Cells(Rows.count, 1).End(xlUp).Row
MsgBox lastRow

lastColumn = ws.Cells(1, Columns.count).End(xlToLeft).Column
MsgBox lastColumn

With ws.UsedRange
    MsgBox (lastRow + lastColumn)
End With

End Sub
Any help or suggestions appreciated both in helping with my problem or best practices that I might be doing in an inefficient manner.

-Trent
 

spencer_time

New Member
That worked like a charm, thank you Joe4. I knew it had to be something simple.

Would that same method work for the workbook?
Code:
Set wb = ActiveWorkbook
or would I want something like this:
Code:
Set wb = ActiveBook
EDIT: I wrote that in haste after reading your reply. After a quick search I see that it is the first solution Set wb = ActiveWorkbook.

Thanks again,
Trent
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
I think I usually use ActiveWorkbook for the workbook one. So I think you are fine there.
 

Some videos you may like

This Week's Hot Topics

Top