Define Last Row as per the report-CodePudding

I am working on a code that performs a certain number of steps on different reports. The reports contain different number or rows every time and in some cases , the reports also contain a hidden row below the last row with data. My code works fine on reports that have a hidden row but it does not work well on reports that do not have a hidden row. For the reports that do not have a hidden row, it leaves one row blank. It works well until I define LR2. I would like to define LR2 in so that it does not consider the hidden row as a row containing data so that my code works uniformly on reports containing hidden row as well as not containing hidden row. Please see the image of the file that has a hidden row. In this case, row number 64 is hidden but in some cases there are no hidden rows below the grey row which is supposed to be the last row. Please assist me writing a single code to work for both scenarios

Dim LR2 As Long
LR2 = ActiveSheet.UsedRange.Rows.Count - 2
ActiveSheet.Range("A6:A" & LR2).Copy ActiveSheet.Range("B6:B" & LR2)
Application.CutCopyMode = False

ActiveSheet.Range("B6:B" & LR2).Select
Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2```

CodePudding user response：

UsedRange is not always a reliable to find the last row, so try something like this:

Sub Tester()

    Dim ws As Worksheet, rng As Range
    Dim lr As Long
    
    Set ws = ActiveSheet
    
    lr = LastUsedRow(ws)
    If ws.Rows(lr).Hidden Then lr = lr - 1 'skip last row if hidden
    
    Set rng = ws.Range("A6:A" & lr)
    Debug.Print "copying", rng.Address
    rng.Copy rng.Offset(0, 1) 'copy to colB
    rng.Offset(0, 1).Replace What:="-", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

End Sub

'find the last used row on a worksheet
Function LastUsedRow(ws As Worksheet)
    Dim f As Range
    Set f = ws.Cells.Find(What:="*", After:=ws.Range("A1"), _
                    LookAt:=xlPart, LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Not f Is Nothing Then LastUsedRow = f.Row 'otherwise zero
End Function