VBA - IF Autofilter Contains No Records

aeddipa

Active Member
Joined
Jun 1, 2009
Messages
337
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:

Code:
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,197
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

Active Member
Joined
Jun 1, 2009
Messages
337
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.

Code:
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,197
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

Active Member
Joined
Jun 1, 2009
Messages
337
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!
 

Forum statistics

Threads
1,078,499
Messages
5,340,740
Members
399,393
Latest member
farlow

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top