VBA to Find Next to Last Cell Containing Data in a Column

[LtCmdrData]

Hello,

I have some VBA code that filters a sheet with an outline turned on. I have one column sub and grand totaled with some blanks so I am doing a VLOOKUP to bring in some of the missing data. I can filter for the blanks and use SpecialCells(xlCellTypeVisible) to only VLOOKUP the empty cells but now my problem. This includes the last cell of data (Grand Total amount) and wipes it out. What VBA code do I use so fill in the missing blanks but exclude the Grand Total cell? Here is my code.

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Report").Select
Range("A4").Select
Selection.AutoFilter
ActiveSheet.Range("$A$4:$Y" & LR).AutoFilter Field:=2, Criteria1:="<>"
ActiveSheet.Range("$A$4:$Y" & LR).AutoFilter Field:=3, Criteria1:="="

'Find the first empty cell in column C goes here
With Worksheets("Report").AutoFilter.Range
Range("C" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'PO Freight Opportunity'!R4C8:R1000C22,15,FALSE)"

ActiveCell.Copy
Range("C5:C" & LR).SpecialCells(xlCellTypeVisible).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Selection.AutoFilter

Any help and suggestions would be appreciated.

 

[Takae]

Hope this helps.

Sub test()
Dim LR As Long
Dim wsR As Worksheet
Set wsR = Sheets("Report")
With wsR
     LR = .Range("A" & Rows.count).End(xlUp).row
    .Range("A4").AutoFilter Field:=2, Criteria1:="<>"
    .Range("A4").AutoFilter Field:=3, Criteria1:="="
    .Range(.Range("C4"), .cells(LR - 1, 3)).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=VLOOKUP(RC[-1],'PO Freight Opportunity'!R4C8:R1000C22,15,FALSE)"
    .Range("A1").AutoFilter
    .Range(.Range("C4"), .cells(LR - 1, 3)).Value = .Range(.Range("C4"), .cells(LR - 1, 3)).Value
 End With
End Sub