Code to recognise AutoFilter returning nil records

[moogthemoog]

Hi

I've got VBA to Autofilter records, and to copy the filtered results to another sheet.
When no records are returned, the spreadsheet crashes.

Is there any code to identify if the number of records returned is 0, then go to a specific line of code?

I couldn't find anything completely relevant on the boards, or on the web, for that matter.

Thanks for your help, in advance.

Relevant area of code:

Sheets("MCS").Select
    ActiveSheet.Range("$A$13:$XFD$13").AutoFilter Field:=5, Criteria1:="CANCELLED"
    Range("B13").Offset(rowoffset:=1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.Offset(columnoffset:=endco - 2)).Copy 'selects to the end of the columns of data

I need something here to stop the routine/goto ErrHandler if the records returned = 0.

Full code, for completeness:

Sub mcrMoveCancelled()
' Move cancelled contracts from MCS to CANC

    'Turn off screen updating
    Application.ScreenUpdating = False
    
    'Turn off Events enable
    Application.EnableEvents = False
    
    'Turn off Auto Calculate - to speed up application
    Application.Calculation = xlCalculationManual
    
    On Error GoTo ErrHandler
    
    '< endco: this picks the last column to be copied over
    Sheets("MCS").Select
    Range("A13").Select
    endco = Range("XFD13").Column
    ActiveCell(1, endco).Select
    Selection.End(xlToLeft).Select
    endco = ActiveCell.Column
    '/endco>
    
    Sheets("MCS").Unprotect
    Sheets("CANC").Unprotect
    
    If Sheets("MCS").FilterMode = True Then
    Sheets("MCS").ShowAllData
    End If
    
    If Sheets("CANC").FilterMode = True Then
    Sheets("CANC").ShowAllData
    End If
    
    Sheets("MCS").Select
    ActiveSheet.Range("$A$13:$XFD$13").AutoFilter Field:=5, Criteria1:="CANCELLED"
    Range("B13").Offset(rowoffset:=1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.Offset(columnoffset:=endco - 2)).Copy 'selects to the end of the columns of data
    
    Sheets("CANC").Select
    Range("B30163").Select
    Selection.End(xlUp).Offset(rowoffset:=1).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Selection.Locked = True 'To protect cells
    
    '< Sort CANC
    Range("B13").Select
    Range(Selection, Selection.Offset(columnoffset:=(endco - 2))).Select 'titles
    Range(Selection, Selection.End(xlDown)).Select
    
    'Sort the data
    'to sort the data by Company ID, Cancellation Date, Parent ID, Debtor ID and MCS No (4 keys)
    'macro won't do more than 3, apparently, so need to sort in two goes:
    'Debtor ID and MCS no first
    ' then Company ID, Cancellation Date, and Parent ID
      
    Selection.Sort Key1:=Range("S13"), Order1:=xlAscending, Key2:=Range("C13") _
        , Order2:=xlAscending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
        
    Selection.Sort Key1:=Range("B13"), Order1:=xlAscending, Key2:=Range("K13") _
        , Order2:=xlAscending, Key3:=Range("R13") _
        , Order3:=xlAscending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
    'Sort CANC >
    Range("B14").Select
    ActiveSheet.Protect , DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFiltering:=True
    
    Sheets("MCS").Select
    Application.CutCopyMode = False
    Selection.EntireRow.Delete 'Delete Cancelled contracts on MCS
    Sheets("MCS").ShowAllData
    Range("AO14").Select '(Last Sales Invoice Number)

ErrHandler:
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub

Thanks
Jon

 

[Peter_SSs]

Rather than trying to determine if there are any rows in the AutoFilter, I often use code like this

Dim LR As Long

With Sheets("MCS")
    LR = .Range("E" & .Rows.Count).End(xlUp).Row
    With .Range("A13:XFD" & LR)
        .AutoFilter Field:=5, Criteria1:="CANCELLED"
        .Offset(1).Copy

1. Find the bottom (LR = last row) of the data in the column to be filtered, before the filter is applied.

2. Filter the range (the range goes from the header row to LR)

3. Offset the range down by 1 row then copy. This always ensures there is at least one row (a blank one) to copy. Copying an extra blank row, or just a blank row if there are no others, doesn't do any harm in the Paste destination.


Edit: You may have noticed your code was previously cut off part way down. That occurred because of your use of the < sign in your code (in comments) with no space immediately after the < sign. This caused the board software to think they were HTML tags & messed up the thread. Keep that in mind in future when posting code.

 

[Boller]

Alternatively, could use Find to see whether "CANCELLED" is there.

If yes, do the filter. If not, exit (or do something else).

 

[moogthemoog]

Many thanks Peter, that is useful to consider.

A further complication is that after copying the filtered rows to the CANC sheet, I need to delete the filtered rows from the MCS sheet.

By using the Offset function, if there are no filtered results obtained, this will delete the title row.

I can probably tweak the code you've provided, but is there a way of determining the number of rows in an Autofilter?

Thanks
Jon

 

[moogthemoog]

Boller, that is also a great workaround, thank you, and I'll give it a go.

Silly of me not to think of it!

Cheers
Jon

 

[Richard Schollar]

Hi

You could count the filtered rows and only proceed if rows>1 (header row will always be returned) so:

With Activesheet.Autofilter.Range
  If .SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
     'autofilter has returned rows
  Else
     'no rows returned!
  End If
End With

 

[moogthemoog]

Many thanks, both your solutions worked for me.

Thanks for your time.

Jon

 

[Peter_SSs]

A further complication is that after copying the filtered rows to the CANC sheet, I need to delete the filtered rows from the MCS sheet.

By using the Offset function, if there are no filtered results obtained, this will delete the title row.

Jon

I think you have received what you want already, but I just want to clarify the above issue regarding my suggestion. I'm not sure if you checked with deletion with my code structure but the whole point of the Offset(1) is to move a row below the headings. By changing .Offset(1).Copy to .Offset(1).Entirerow.Delete in my code, and assuming the headings are in row 13 it should not delete the headings, even if the filter finds no rows matching the filter criteria.

What it will do is delete a row from your sheet from below the filtered data. If you do have other blocks of data on your sheet below the filter range this could cause an issue but if not there should not be a problem.

Here is an example where I've used this idea. To me, it isn't worth checking every time through the loop to see if the ID on Sheet 'IDList' actually occurs on sheet 'Estimated Data Log'. Just filter, offset, copy (& delete if you want) and if it copies a few blank rows, you'll never tell with the finished product. http://www.mrexcel.com/forum/showthread.php?t=505770