Delete ALL non visible cells

Shind

Board Regular
Joined
Feb 12, 2006
Messages
191
Hi,

After I have applied a number of filters on an excel spreadsheet using vba, how can I remove/delete the data that is NOT Visible. I know the below code will select ALL visible cells but is there a way to amend this code to delete all non visible.

Range("A2:A" & Range("AY65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi,

After I have applied a number of filters on an excel spreadsheet using vba, how can I remove/delete the data that is NOT Visible. I know the below code will select ALL visible cells but is there a way to amend this code to delete all non visible.

Range("A2:A" & Range("AY65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
Consider if it would be feasible to reverse your filters (so that the things you DON'T want are visible), delete the visible cells/rows, then remove the filters.

If you need help with the code, post your existing code and/or describe where your data is and what data you want retained (that is, what filters you are currently applying).
 
Upvote 0
oK i'll just reverse the filters. using the code i provided, how would i deleted the visible information
 
Upvote 0
oK i'll just reverse the filters. using the code i provided, how would i deleted the visible information
If your headings are in row 1, then
Code:
Range("A2:A" & Range("AY65536").End(xlUp).Row).EntireRow.Delete
should do it. (In a case like this, you usually do not have to specify that is is only the visible rows that you are deleting). Then remove the AutoFilter.
 
Upvote 0
With the auto-filtered engaged, and displaying your requested data
I generally select all the records by using the row-headers (clicking on the first row (header) and dragging down to the last record, then edit, delete (entire row).
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top