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)
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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).
 

Shind

Board Regular
Joined
Feb 12, 2006
Messages
191
oK i'll just reverse the filters. using the code i provided, how would i deleted the visible information
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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.
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,483
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,123,511
Messages
5,602,081
Members
414,500
Latest member
kevdragon1

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
Top