You are here:

Excel/Excel 2007 VBA Autofilter Columns

Advertisement


Question
Hi Tom,

I am using Excel 2007 for Windows.  

I would like to use autofilter with a VBA solution to filter 60 columns independently of each other.

My data spans many rows and columns: D9 to BK dynamic, expanding rows.  Data to be filtered starts in column “D” (autofilter field 4) and ends in column “BK” (autofilter field 63).  The autofilter criteria1 is a numeric value that is different for each column – located in row 6 of each column.  

This is the automated process I’m trying to achieve:

1.   Filter column “D” using the numeric criteria in cell D6
2.   Other tasks and code to be run
3.   Copy the visible filtered cells in column “D” and pastespecial column widths, and values and number formats to Sheet2 cell D9
4.   VBA code showalldata  
5.   DO  1, 2, 3 and 4 again until all 60 columns have been processed.

I’ve got into a muddle with my code:

Dim rng As Range
Dim Lastrow As Long
Dim I As Integer
   
   Sheets("Sheet1").Activate
   
    'check for filter, turn on if none exists
If Not Sheets("Sheet1").AutoFilterMode Then
Sheets("Sheet1").Range("D8").AutoFilter
End If
      
   With Sheets("Sheet1")
       Lastrow = .Range("B7")
       Set rng = .Range("D8" & ":BK" & Lastrow)
       
       rng.AutoFilter
       For I = 4 To 63   
      rng.AutoFilter Field:=I, Criteria1:=.Cells(6, I).Value

   Call Amber
   
   rng.SpecialCells(xlCellTypeVisible).Copy
   Sheets("Sheet2").Cells(9, I).PasteSpecial Paste:=xlPasteColumnWidths
   Sheets("Sheet2").Cells(9, I).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
   Application.CutCopyMode = False
   ActiveSheet.ShowAllData     
      Next I
       rng.AutoFilter Field:=I, Criteria1:=.Cells(6, I).Value
 End With
End Sub

Hope you can help.
Thanks Sam

Answer
Sam,
It is not clear what you want.  when I look at your code, you are looping from 4 to 63 and copying a 60 column Range fromm sheet A to D9, then to E9, to F9, to G9 and so forth.  But when you paste into E9, you overwrite columns most of the data pasted starting in D9.  

So I assume you only want to copy column D visible values to column D of sheet2.  Then remove the filter and only filter column E and copy those visible value to column E of sheet2 and so forth.  So I have alterer rng to refer to D8:D_Lastrow and then filter just that column and copy the visible cells to D9 of sheet2.  Now process just column E, then column F and so forth.

This is consistent with the code you show for pasting - but naturally there is no row identity across columns  (the values in row 11 of columns D, E and F of Sheet2 may or may not have come from the same row in sheet1.  High probability that they did not.

So this is my guess at what you want and it worked for me as I expected it to work.

Column D of sheet2 has the same value (the filter value) in all rows up to the number of occurrences of that value in column D of sheet1.  Same for column E and so forth.  

I don't know if that is what  you want or not.

Sheets("Sheet1").Activate
   
    'check for filter, turn on if none exists
Sheets("Sheet1").AutoFilterMode = False

    
   With Sheets("Sheet1")
       Lastrow = .Range("B7")
       Set rng = .Range("D8" & ":D" & Lastrow)

       
   For I = 4 To 63
     Sheets("Sheet1").AutoFilterMode = False
     rng.Offset(0, I - 4).AutoFilter Field:=1, Criteria1:=.Cells(6, I).Value
   'Call Amber
   
     rng.Offset(0, I - 4).SpecialCells(xlCellTypeVisible).Copy
     Sheets("Sheet2").Cells(9, I).PasteSpecial Paste:=xlPasteColumnWidths
     Sheets("Sheet2").Cells(9, I).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
     Application.CutCopyMode = False
   Next I

   End With
 Worksheets("Sheet1").AutoFilterMode = False
End Sub

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.