You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Excel 2007 VBA Autofilter Columns

Advertisement

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

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

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Tom, Your assumptions were all correct. The code does exactly what I need. Brilliant! Thank you so much for your time and effort. Sam |

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

Answers by Expert:

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.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.