Error Checking in Excel
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: VBA Macro to Autofilter based on Sheet 2 list.

  1. #11
    New Member
    Join Date
    Mar 2016
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Macro to Autofilter based on Sheet 2 list.

    Ok, so I think we're good. Instead of worrying about filtering out blanks, I just added a delete rows command.

    Code:
    Sub DeleteRows()    Range("M:M").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End Sub

  2. #12
    New Member
    Join Date
    Mar 2016
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Macro to Autofilter based on Sheet 2 list.

    Duplicate
    Last edited by Bluesguy07; Jun 14th, 2018 at 11:11 AM.

  3. #13
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    UK
    Posts
    240
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Macro to Autofilter based on Sheet 2 list.

    My apologies. My code as written will have included blanks as the array had too many elements. This modified code will only set up the array with the correct number of elements:
    Code:
    Sub InclusiveFilter()Dim IncludeArray() As String
    With Sheets("Sheet2")
    Dim lastrow As Long
    Dim i As Long
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    ReDim Preserve IncludeArray(lastrow - 2)
    ' assumes a header row so start in row 2
    For i = 2 To lastrow
    IncludeArray(i - 2) = .Range("A" & Format(i)).Text
    Next i
    
    
    End With
    
    
    With Sheets("Sheet1")
    ' assumes the filtered area is named table1
        .Range("List1").AutoFilter Field:=13, Criteria1:=IncludeArray, Operator:=xlFilterValues
    End With
    End Sub

  4. #14
    New Member
    Join Date
    Mar 2016
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Macro to Autofilter based on Sheet 2 list.

    Quote Originally Posted by jmacleary View Post
    My apologies. My code as written will have included blanks as the array had too many elements. This modified code will only set up the array with the correct number of elements:
    Code:
    Sub InclusiveFilter()Dim IncludeArray() As String
    With Sheets("Sheet2")
    Dim lastrow As Long
    Dim i As Long
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    ReDim Preserve IncludeArray(lastrow - 2)
    ' assumes a header row so start in row 2
    For i = 2 To lastrow
    IncludeArray(i - 2) = .Range("A" & Format(i)).Text
    Next i
    
    
    End With
    
    
    With Sheets("Sheet1")
    ' assumes the filtered area is named table1
        .Range("List1").AutoFilter Field:=13, Criteria1:=IncludeArray, Operator:=xlFilterValues
    End With
    End Sub
    Thanks! This works perfectly! I'd rather filter out blanks than have to delete content anyways. I appreciate everything you heroes of the excel world do. I'd mark the thread as solved, but I am unsure how to do that.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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