spencer_time
Board Regular
- Joined
- Sep 19, 2019
- Messages
- 55
- Office Version
- 365
- 2016
- 2007
- Platform
- 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:
The following is the NON-WORKING code with me trying to use a variable for the active sheet:
Any help or suggestions appreciated both in helping with my problem or best practices that I might be doing in an inefficient manner.
-Trent
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