How to properly activate a sheet in a workbook
I am trying to get the last column and last row for a given worksheet in my workbook. Below is the code I am using to get these values. This code works fine (since I am getting the correct number of columns and rows) as long as I click on the sheet and then run Sub LastRowCol () .
Function LastUsedRow_Find() As Integer
Dim rng As Range
Set rng = ActiveSheet.Cells
LastUsedRow_Find = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
End Function
Function LastUsedColumn_Find() As Integer
Dim rng As Range
Set rng = ActiveSheet.Cells
LastUsedColumn_Find = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
End Function
Sub LastRowCol()
MsgBox "last row -> " & LastUsedRow_Find()
MsgBox "last col -> " & LastUsedColumn_Find()
End Sub
But let's say I have, for example, several sheets. I am trying to pass the leaf index to the methods as shown below. Then I try to activate the sheet for the given index as I am using ActiveSheet a few lines later. The code works without exception. But I am not getting the correct number of columns and rows in the msg field.
Function LastUsedRow_Find(wksIndex As Integer) As Integer
ActiveWorkbook.Sheets(wksIndex).Activate
Dim rng As Range
Set rng = ActiveSheet.Cells
LastUsedRow_Find = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
End Function
Function LastUsedColumn_Find(wksIndex As Integer) As Integer
ActiveWorkbook.Sheets(wksIndex).Activate
Dim rng As Range
Set rng = ActiveSheet.Cells
LastUsedColumn_Find = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
End Function
Sub LastRowCol()
MsgBox "last row -> " & LastUsedRow_Find(1)
MsgBox "last col -> " & LastUsedColumn_Find(1)
End Sub
Can someone please tell me what I might be doing wrong or what I might need to change? Thanks to
source to share
Instead of passing the index, why don't you pass Worksheet Object
.
Function LastUsedRow_Find(sh As Worksheet) As Variant
Dim rng As Range
Set rng = sh.Cells.Find(What:="*", _
After:=sh.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not rng Is Nothing Then LastUsedRow_Find = rng.Row
End Function
The same should be done with your other function.
Also, I modified it slightly to handle blank sheets.
Thus, the function above returns the last line number if available, and simply empty if not.
Then call your function like this:
Sub LastRowCol()
MsgBox "last row -> " & LastUsedRow_Find(Sheets("Sheet1"))
MsgBox "last col -> " & LastUsedColumn_Find(Sheets("Sheet1"))
End Sub
source to share
Your code does work fine, but there is a modified version here that will be the Debug.Print
found cell address for the last row / column. I also do not activate the leaf as an extra one. I also do not assign the whole cell of the workbook to a variable, but only the found result.
Function LastUsedRow_Find(wksIndex As Integer) As Integer
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets(wksIndex)
Dim rgFound As Range
Set rgFound = ws.Cells.Find(What:="*", After:=ws.Cells(1), _
Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False)
'return first row if worksheet is blank
If rgFound Is Nothing Then
LastUsedRow_Find = 1
Else
LastUsedRow_Find = rgFound.Row
Debug.Print "Last row address: " & rgFound.Address
End If
End Function
Function LastUsedColumn_Find(wksIndex As Integer) As Integer
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets(wksIndex)
Dim rgFound As Range
Set rgFound = ws.Cells.Find(What:="*", After:=ws.Cells(1), _
Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False)
'return first column if worksheet is blank
If rgFound Is Nothing Then
LastUsedColumn_Find = 1
Else
LastUsedColumn_Find = rgFound.Column
Debug.Print "Last column address: " & rgFound.Address
End If
End Function
Sub LastRowCol()
MsgBox "last row -> " & LastUsedRow_Find(1)
MsgBox "last col -> " & LastUsedColumn_Find(1)
End Sub
source to share