0

If I highlight the entire first row and filter it, I shouldn't need to repeat the process every time I add a new column. Is there a way around that? I could run a macro, but that's still an extra step every time I add a new column.

For example, if I have a column named "Types of apples" and I highlight the entire first row and click on Data > Filter, then when I create another column for "Types of oranges," that column should be filtered automatically because I already told Excel I wanted the entire first row filtered.

Version: Excel 365 ProPlus, Version 1750

  • 1
    Turn it into a table. – Selkie Dec 8 '17 at 21:00
  • 1
    If you insert a column in someplace other than the vertical end of your sheet, Excel will apply a filter for the added column, assuming you already have filter applied to the sheet. However, if you add a column, either using right-click Insert or Insert > Sheet Columns, at the end of a sheet, then Excel doesn't apply a filter for that column. As @Selkie says, the only way around this is to change the sheet to a bona fide table. – BillDOe Dec 8 '17 at 21:20
0

Format your data range as a table.

  1. Select your data range
  2. On the Home tab, click Format as Table.\ Screenshot of 'Format as table' button pressed
  3. Click the table style that you want to use

Not only can you add extra columns to the end of your data and have filters automatically applied, but you can also add extra lines of data at the botton and your table will auto grow.

0

I would like to suggest two choices.

1. Select the Data Range, Right Click, & hit Filter then Reapply.

2. This VBA code will help you to apply a filter when you add or remove a new column.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As range)

  Dim ws As Worksheet
  Dim rng As range
  Set ws = Sh

  If ws.AutoFilterMode Then

     With ws.AutoFilter.range

        If (Target.Column < .Column) And (Target.Column + Target.Columns.count >= 
           .Column - Target.Columns.count) Or (Target.Column >= .Column + .Columns.count) Then
           Set rng = ws.AutoFilter.range
           ws.AutoFilter.range.AutoFilter
           ws.range(Target, rng).AutoFilter
        End If

     End With

  End If
End Sub

I hope this helps you. I've posted this solution after it was tested by me.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.