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

[xander1981]

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.



'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

[Admin]

Hi

Try this


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

[xander1981]

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.

[Admin]

Hi

If you are looking for numbers in Col G, replace


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

with


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

[xander1981]

Work great, thanks you :)