Find last row with application.match

[Rowland Hamilton]

Folks:

How can I find last row using application.match and an if statement in case it doesn't match?
My boss doesn't want me to use UsedRange or xlUP.

Here is my code I can't get to work:

Sub test2()
Dim iRowFinal As Integer
'shtSummary.Activate
If Not IsError(ShtSumary.Application.Match("Overall Results", Columns(11), 0).Row) Then
    iRowFinal = ShtSumary.Application.Match("Overall Results", Columns(11), 0).Row
End If

Stop
End Sub

FYI: I know these work, but can't use them:

Dim iRowFinal As Integer
Dim LastRowF As Integer
Dim LastRowSC As Integer
Dim LastRowUR As Integer

LastRowUR = ActiveSheet.UsedRange.Rows.Count
LastRowF = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
iRowFinal = Range("A" & Rows.Count).End(xlUp).Row
LastRowSC = Cells.SpecialCells(xlCellTypeLastCell).Row

Thanks - Rowland

 

[Rowland Hamilton]

Okay, so first of all, should have used this for match formula:

iRowFinal = shtSummary.Application.Match("Overall Results", _
shtSummary.Range("K:K"), 0)

while I had a .row that was redundant so it didn't work properly (although oddly it worked sometimes, but ignored the sheet assignment).

 

[Peter_SSs]

Rowland

Your first code and description do not seem to agree with the second codes that you say would work. The second codes would be ways to find the last used row on a sheet. Your first code and "in case it doesn't match" seem to indicate to me that you may be looking for the last occurrance of "Overall Results" rather than the last used row in column K.

In any case, if your boss is forcing you to use a difficult and inefficient code when there is a simple alternative then you are in a difficult position. Do you know the reason behind this seemingly pointless restriction?

 

[Rowland Hamilton]

Sub test2()
Dim iRowFinal As Integer
Dim LastRowF As Integer
Dim LastRowSC As Integer
Dim LastRowUR As Integer
Dim LastrRowM As Integer
Dim LastRowEU As Integer

'shtSummary.Activate
If Not IsError(shtSummary.Application.Match("Overall Results", shtSummary.Range("K:K"), 0)) Then
    iRowFinal = shtSummary.Application.Match("Overall Results", shtSummary.Range("K:K"), 0)

End If

LastRowUR = ActiveSheet.UsedRange.Rows.Count
LastRowF = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastRowEU = Range("A" & Rows.Count).End(xlUp).Row
LastRowSC = Cells.SpecialCells(xlCellTypeLastCell).Row
LastrRowM = shtSummary.Application.Match("Overall Results", shtSummary.Range("K:K"), 0)

Stop
End Sub

 

[Rowland Hamilton]

Figured out my if/then statement. - Thanks

 

[Rowland Hamilton]

Peter: They think using EndUp will mess up the scroll bar and they don't want to have to save in order to right-size it. I have used it and it never messes up my scroll bar so I don't know what they are talking about. From what I've seen on the internet, you can reset the scroll bar without saving by deleting unused rows (below code) but that is a bit much. Just doing ActiveSheet.UsedRange alone doesn't work if the bar is stretched out by seemingly blank cells.

UsedRange restriction is in case the end user types something in the sheet below the actual range we want to use.

Sub DeleteUnusedFormats(wksScroll As Worksheet)
'routine resets scrollbar by deleting unused rows below data
Dim lLastRow As Long
Dim lLastColumn As Long
Dim lRealLastRow As Long
Dim lRealLastColumn As Long

    'note: May need to place sheet.activate code before this sub is called
    wksScroll.Activate
    With Range("A1").SpecialCells(xlCellTypeLastCell)
        lLastRow = .Row
        lLastColumn = .Column
    End With

    lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , _
        xlByRows, xlPrevious).Row

    lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , _
        xlByColumns, xlPrevious).Column

    If lRealLastRow < lLastRow Then
        Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete
    End If
    
    'Column delete will mess up named ranges = column when column is unused
'    If lRealLastColumn < lLastColumn Then
'        Range(Cells(1, lRealLastColumn + 1), Cells(1, lLastColumn)) _
'            .EntireColumn.Delete
'    End If

    ActiveSheet.UsedRange   'resets LastCell
    
    'cursor default to top left cell = A1
    'wksScroll.Range("A1").Select
    'Application.Goto Reference:=Range("a1"), Scroll:=True
End Sub