Announcement

Collapse
No announcement yet.

Determine, In VBA Macro, If Worksheet Has Auto Filtered Rows

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Determine, In VBA Macro, If Worksheet Has Auto Filtered Rows



    Hi guys,

    I've cobbled together a macro to extract data from several worksheets and paste into a new worksheet based on AutoFilter criteria. Works pretty darn well, except for one major problem: If a worksheet has no records matching the AutoFilter criteria, all the records in that worksheet are selected and pasted. Can anyone help me fix that. Here's the macro:

    Code:
    Sub AEF()
         '
         ' macro to select filtered data and paste to new sheet
         ' Macro by Steve Terek on 20 Nov 2009
         '
         
         '
        Dim i As Integer
        Dim rngData As Range
    
    
        Application.ScreenUpdating = False
        
        Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = "AEF"
        
        Sheets("Concepts").Select
        Rows("1:2").Select
        Selection.Copy
        
        Sheets("AEF").Select
        ActiveSheet.Paste
        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    
    
        For i = 2 To 10
            
            Sheets(i).Select
    
                Range("A1").Select
                Selection.AutoFilter Field:=7, Criteria1:="AEF"
                
                Set rngData = ActiveSheet.Range("A1").CurrentRegion
    
                        Set rngData = rngData.Resize(rngData.Rows.Count - 2)
    
                        'Move new range down to Start at the fisrt data row.
    
                        Set rngData = rngData.Offset(2, 0)
                        rngData.Select
                        Selection.Copy
                
                    
                Sheets("AEF").Select
                Range("A65536").End(xlUp).Offset(1, 0).Select
                ActiveSheet.Paste
                
                Sheets(i).Select
                Range("A1").Select
                Selection.AutoFilter
    
                        
    
        Next i
    
    
        Sheets("AEF").Select
        Range("A1").Select
    
        Application.CutCopyMode = False
    
    End Sub
    Cheers,
    Steve

  • #2
    Re: Ignore Blanks In Autofilter

    Try something like
    Code:
    If Sheet1.AutoFilter.FilterMode = True Then
    
    
    End If
    OR
    Code:
    On Error Resume Next
        If Not Sheet1.AutoFilter.Range.SpecialCells(xlCellTypeVisible) Is Nothing Then
    On Error GoTo 0
    
        End If

    Comment


    • #3
      Re: Determine, In VBA Macro, If Worksheet Has Auto Filtered Rows

      Thanks for the reply, Dave. I didn't know how to implement your suggestion. I found a way around the problem by pasting first to a temporary sheet and re-filtering the data to get the desired result.

      Comment


      • #4


        Re: Determine, In VBA Macro, If Worksheet Has Auto Filtered Rows

        Surely you could have figured it out??

        Comment

        Working...
        X