check if next active row after filter is blank in specific column

Hello, I am having trouble when the cell in column G is blank after filtering three columns to three different criteria. If the first active row is blank after filter I need a message box. I am getting the error, no cells were found. Any help would be very appreciated.

Code:

---

'Filter to Line Rate, copy and paste
With ActiveWorkbook.Worksheets("Rates Matrix").Select
    Sheets("Rates Matrix").Range("$A$1:$F$5000").AutoFilter Field:=1, Criteria1:=Destination
    Sheets("Rates Matrix").Range("$A$1:$F$5000").AutoFilter Field:=3, Criteria1:=Carrier
    Sheets("Rates Matrix").Range("$A$1:$F$5000").AutoFilter Field:=4, Criteria1:=Size
    Range("G2:G1000").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Offset(0, 0).Select
    Selection.Copy
    ActiveSheet.Range("A2").Select
    End With
    Sheets("Rates").Select
    CarrierResult.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        ActiveSheet.Range("G7").Select

---

Hi

Try this

Code:

---

    Dim r As Range
   
    'Filter to Line Rate, copy and paste
    With ActiveWorkbook.Worksheets("Rates Matrix").Range("$A$1:$F$5000")
        .AutoFilter Field:=1, Criteria1:=Destination
        .AutoFilter Field:=3, Criteria1:=Carrier
        .AutoFilter Field:=4, Criteria1:=Size
        On Error Resume Next
        Set r = .Cells(1).Offset(1, 6).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
    End With
    If Not r Is Nothing Then
        r.Copy
        CarrierResult.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        ActiveSheet.Range("G7").Select
    Else
        MsgBox "No records found for the selected criteria", vbInformation
    End If

---

Many thanks but even when there is no visible data after filter a blank cell is being copied over so the msgbox is never generated. When I ran the code line by line 'r' did not have a numerical value but was not 'Nothing' as I would expect it to be.

Hi

If you are looking for numbers in Col G, replace

Code:

---

Set r = .Cells(1).Offset(1, 6).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)

---

with

Code:

---

Set r = .Cells(1).Offset(1, 6).Resize(.Rows.Count - 1, 1).SpecialCells(2, 1)

---

Work great, thanks you :)