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

+3


source to share


2 answers


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

      

+2


source


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

      

+1


source







All Articles