Define Last Row as per the report

[Zain_inout]

Hi,

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.

Below is my code:

Sub RetireeCheck()
On Error Resume Next
Dim FilePath As String
Dim wb As Workbook
FilePath = Application.GetOpenFilename
If Not FilePath = "False" Then Set wb = Application.Workbooks.Open(FilePath)
If wb Is Nothing Then Exit Sub
With wb
 .Activate
 my_file = ActiveWorkbook.FullName
 new_name = Replace(my_file, "0_Original", "1_RetireeCheck")
 ActiveWorkbook.SaveAs new_name
 Application.ScreenUpdating = False

 
 
' autofit columns U,B,C and D
.ActiveSheet.Columns("U:U").EntireColumn.AutoFit
.ActiveSheet.Columns("B:B").EntireColumn.AutoFit
.ActiveSheet.Columns("C:C").EntireColumn.AutoFit
.ActiveSheet.Columns("D:D").EntireColumn.AutoFit


'Sorting on total ytd hours
ActiveWorkbook.Worksheets("rptSOAEEContributionSummaryRepo").Select
Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count
ActiveWorkbook.Worksheets("rptSOAEEContributionSummaryRepo").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("rptSOAEEContributionSummaryRepo").Sort.SortFields. _
        Add2 Key:=Range("R6:R" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("rptSOAEEContributionSummaryRepo").Sort
        .SetRange Range("A5:W" & LR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
'Filtering 0 hours and EE Paid
ActiveWorkbook.Worksheets("rptSOAEEContributionSummaryRepo").Select
ActiveSheet.Range("A5:W" & LR).AutoFilter Field:=18, Criteria1:="-"
ActiveSheet.Range("A5:W" & LR).AutoFilter Field:=19, Criteria1:="-"

'Deleting 0 hours and EE Paid
    ActiveSheet.Range("A6:W" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Select
ActiveSheet.Range("A6:W" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete

   'Removing the filter
   ActiveSheet.Range("A5:W" & LR).AutoFilter Field:=18
  ActiveSheet.Range("A5:W" & LR).AutoFilter Field:=19
 
  ' Creating new columns
ActiveSheet.Range("B:B").Insert
ActiveSheet.Range("B:B").Insert
ActiveSheet.Range("U:U").Insert

'Naming the columns
ActiveSheet.Range("B5").Select
ActiveCell.FormulaR1C1 = "SIN2"
ActiveSheet.Range("C5").Select
ActiveCell.FormulaR1C1 = "Retireecheck"
ActiveSheet.Range("U5").Select
ActiveCell.FormulaR1C1 = "Employee Contribution Rate Paid"
     
'Copying SIN to SIN2 and removing "-"
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
       
' taking vlookup for retireecheck in column C

ActiveSheet.Range("C6").Formula = "=VLOOKUP(RC[-2],'[CompleteListOfRetirees (2020-03-02 1130 AM) - Updated Manually.xlsm]Summary'!R1C[-1]:R59122C[-1],1,0)"
ActiveSheet.Range("C6").AutoFill Destination:=ActiveSheet.Range("C6:C" & LR2)

'Calculating EE cont rate paid
ActiveSheet.Range("U6").Formula = "=RC[+1]/RC[-1]"
ActiveSheet.Range("U6").AutoFill Destination:=ActiveSheet.Range("U6:U" & LR2)

'Save the Retiree check file
ActiveWorkbook.Save

Application.ScreenUpdating = True
End With
End Sub

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

 

[bstory84]

Try this Change where you define LR2

LR2 = Range("A1048576").End(xlUp).Row