Results 1 to 5 of 5

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

  1. #1

    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

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    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.

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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)
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Work great, thanks you

Similar Threads

  1. Replies: 0
    Last Post: 12-24-2013, 01:36 PM
  2. Replies: 8
    Last Post: 07-01-2013, 03:52 PM
  3. Delte a specific column and does not delete the top row
    By jffryjsphbyn in forum Excel Help
    Replies: 1
    Last Post: 06-13-2013, 02:00 PM
  4. Highlight Active Cell’s Row and Column
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  5. Replies: 2
    Last Post: 09-24-2012, 11:19 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •