Active worksheet as variable broken

spencer_time

Board Regular
Joined
Sep 19, 2019
Messages
55
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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:
Upvote 0
I think I usually use ActiveWorkbook for the workbook one. So I think you are fine there.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top