Delete a filtered auxiliary column while maintaining the filtered range.

[stranno]

Hi All,
I want to Autofilter the auxiliary column (Criteria1:="x") and after filtering delete the auxiliary column, while maintaining the filtrered range. It should be done "in place" and not via copy and paste or a temporarily added worksheet. Is that somehow possibe to do?







I added an example workbook.

Stranno

[vcoolio]

Hello Stranno,

Is this what you're after?



Sub Test()

Application.ScreenUpdating = False

With Sheet1.[A1].CurrentRegion
.AutoFilter 1, "<>" & "x"
.Offset(1).EntireRow.Delete
.AutoFilter
End With

Sheet1.Columns(1).Delete

Application.ScreenUpdating = True

End Sub

I've attached your sample with the code implemented. Click on the "RUN" button to see it work.

I hope that this helps.

Cheerio,
vcoolio.

22830

[stranno]

Thanks for your response Vcoolio.
But I wasn't entirely clear i am afraid. I don't want to lose the lines which were filtered out.

my fault. Thanks anyway!

stranno

[p45cal]

Sub Test()
With ActiveSheet
.[A1].CurrentRegion.AutoFilter 1, "x"
.AutoFilter.Range.Offset(1).Columns(1).ClearContents
End With
End Sub
?
It deletes contents of all visible cells in column 1

If you want all cells (visible and invisible) in column one to be cleared then:
Sub Test()
With ActiveSheet
.[A1].CurrentRegion.AutoFilter 1, "x"
For Each cll In .AutoFilter.Range.Offset(1).Columns(1).Cells
cll.ClearContents
Next cll
End With
End Sub

[stranno]

Hi p45cal,

With "delete auxiliary column" I mean delete the entire column, not only the content.

[p45cal]

You can't do it with Autofilter, but you can with Advanced filter.
There are two ways:
1.
Set up a cell somewhere, say cell L1, and copy/paste the header auxiliary column to it.
In cell L2 type an x
Go back to selecting a cell somewhere in the table you want to filter and click Advanced next to Filter (Autofilter) in the Data section of the ribbon.
It should automatically select the table you want to filter. Opt for filtering in place, and for the Criteria range select cells L1:L2, and click OK.
Now you can delete the auxiliary column (and the criteria range if you want) without upsetting the filtering of the table.

2.
Set up a cell somewhere, say cell F24, which has a blank cell above it.
Type this formula into it:
=A2="x"
Go back to selecting a cell somewhere in the table you want to filter and click Advanced next to Filter (Autofilter) in the Data section of the ribbon.
It should automatically select the table you want to filter. Opt for filtering in place, and for the Criteria range select cells F23 (a blank cell) and cell F24 (with the formula in it) immediately below it, and click OK.
Again, you can delete the auxiliary column (and the criteria range if you want) without upsetting the filtering of the table.

[stranno]

Thanks a lot p45cal.
This works fine.