0

I have a macro that creates a separate workbook from a larger document. The number of columns vary on how much data is entered. I would like to add an autofilter to only the currently used columns but every time I add an autofilter I have to put in a range and the range varies. Is there a way to write in the code to only add the filter arrow on the applicable columns and not all of them that could possibly be shown? We are sending the document out to a customer and have to manually unfilter the blank boxes which we would prefer not to do.

Thanks!

dwirony
  • 5,487
  • 3
  • 21
  • 43
Megan Randolph
  • 1
  • Please provide some code in order to help you: https://stackoverflow.com/help/mcve without it. It will be really hard that you would get any support from the community. – Federico Navarrete Apr 12 '18 at 12:43
  • 1
    If you know what columns are 'used', then you can just use `Range.Autofilter` to filter the specified Range. The `.End` method is often useful for skipping over blank columns - e.g. `Sheet1.Cells(1,Sheet1.Columns.Count).End(xlToLeft)` will select the rightmost non-blank cell on row 1 of Sheet1 (Or cell A1 if there is *no* data or *no* blank columns) – Chronocidal Apr 12 '18 at 12:45

1 Answers1

0

Turn your data into an Excel Table using the Ctrl + T keyboard shortcut. These have the autofilter built in, and it automatically gets applied to any new columns in the Table, because Tables expand automatically to accommodate any new data.

Furthermore, because Tables are basically Named Ranges that Excel maintains on your behalf, it is very easy to identify the ranges concerned in VBA, as per my answer at VBA coding to identify and clear data in a specific table

jeffreyweir
  • 4,668
  • 1
  • 16
  • 27