VBA - IF Autofilter Contains No Records

[aeddipa]

Hi all,

I have seen similar posts to this, but cannot figure out how it can relate to my situation. I have an autofilter that copies data from one sheet to another. However, when no autofilter results are listed, I would like it to skip the copying steps and go on to the next autofilter criteria.

Code below:

Range("A1").AutoFilter
    ActiveSheet.Range("$A$1:$J$" & lRow).AutoFilter Field:=11, Criteria1:="=Athletics*", _
        Operator:=xlAnd
    
    Range("C1").Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("C2:J" & lRow).Select
        
    Selection.SpecialCells(xlCellTypeVisible).Select '<---- Error occurs here saying "no data to copy or something like that"
    Selection.Copy
    Sheets("Athletics").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Basically, if the Special Cells (xlCellTyperVisible).Select finds no records, I want it to skip past the pastespecial and go to the rest of my code.

Thanks in advance!

 

[Tom Urtis]

You have several options, but first, why is it that you are specifying columns A:J (to presumably the last row with that lrow variable), but you are filtering for Field 11 which would be column K that is not in your AutoFilter range.

Also, when you say "go on to the next autofilter criteria", are you filtering in that same macro, after this posted code, for other criteria besides Athletics*. Or does the rest of your code have nothing to do with anymore AutoFilters in this macro that what you've shown for Athletics*.

 

[aeddipa]

You have several options, but first, why is it that you are specifying columns A:J (to presumably the last row with that lrow variable), but you are filtering for Field 11 which would be column K that is not in your AutoFilter range.

Also, when you say "go on to the next autofilter criteria", are you filtering in that same macro, after this posted code, for other criteria besides Athletics*. Or does the rest of your code have nothing to do with anymore AutoFilters in this macro that what you've shown for Athletics*.

Hey Tom,

I noticed I made some errors when autofiltering. The code should specify columns A:L (I've fixed this). I am filtering the same field by multiple team names. I'll fill you in on what I am doing. I am part of a softball team and we are trying to assign players to teams based on their last year's team. Field 11 is the column "Last Yr Team". Essentially, there are 10 teams, thus 10 different autofilters that will take place. The columns C through J are then copied which contains player name, email, phone #, mom and dad name etc... I don't want to include the last two columns of data (K:L).

However, if I autofilter the team "Athletics*" and no results show (I have a prior step that does a vlookup on the name in the Sheet for that specific team), I obviously get an error when copying because there is no data to copy. I tried modifying my code, but it does not work. Just to clarify, the vlookup looks if the player is already listed on the team sheet. I then filter on blanks " " so that only players from that team last year who have not already been copied to the team roster, will be available to copy.

Dim lRow As Long
Dim ML As Long
lRow = ActiveSheet.UsedRange.Rows.Count
ML = Sheets(2).Range("C" & Rows.Count).End(xlUp).Row

With ActiveSheet.Range("$A$1:$L$" & lRow)
    .AutoFilter Field:=11, Criteria1:="=Athletics*", Operator:=xlAnd
    .AutoFilter Field:=2, Criteria1:=" ", Operator:=xlAnd '<-- Only players that are not already on the roster
End With
    
If ML > 1 Then '<---errors out here saying no cells to copy. Thought this would work, but I guess I did something wrong. Any ideas?
    Range("C2:J" & lRow).SpecialCells(xlCellTypeVisible).Copy
    Sheets("Athletics").Activate
    Range("A20").FormulaR1C1 = "=COUNTA(R[-19]C[1]:R[-18]C[1])"
    If Range("A20").Value <= 1 Then
    Range("B1").Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Else
    Range("B1").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
Range("A20").ClearContents
End If

I won't include the second piece because it replicates the above code, but with the team name "Tigers" next. So if there are no cells to copy from the autofilter then skip the copy job and go on to the next autofilter code.

 

[Tom Urtis]

There are a few areas where your code can improve in terms of not needing to select or activate ranges, which can be discussed later, but for now, try a test with the code you have as you posted it, except...

Substitute this:

If ML > 1 Then


with this, assuming row 1 is your header row and always visible.

If Range("A1:A" & lrow).SpecialCells(xlCellTypeVisible).count > 1 Then

 

[aeddipa]

I'd take all the advice I can get as far as improving my coding. I have been doing VBA for quite a while, but a bit of a hack job. As I learn, my VBA has got better, but im sure there are still things I do that look like im in 1st grade when it comes to VBA programming.

I inserted the formula as you suggested and it works perfectly! Thank you so much for your much needed assistance Tom!