Deleting unhighlighted cells or Invert Selection.

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175
Hi,
Apologies if this is really easy but I cant find a way to do it.

I have a large amount of data. I autofilter this data to give me the data I require. I would then like to delete all of the other data and just leave the data I filtered.

I dont want to create another worksheet if need be.

I know how to select all the visible cells in a worksheet, is there any way to invert this after selecting them or just of just clearing the rest of the cells?

Thanks for any help.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
dixon1983

Can you just reverse the AutoFilter so that you are filtering data that you do NOT require then delete the resulting visible rows? Then remove the AutoFilter and you will be left with the data you DO require.
 
Upvote 0
I could do but there are too many other fields and they do not always have the same name.

I want to put it into a button that when clicked will do it automatically each day regardless of what the other fields are called
 
Upvote 0
Thanks for that. Only problem is theres going to be a few of us at work that will use this workbook and I dont want to have to ask them to download this add in.

It also takes a bit of time to get the go ahead for any add ins etc due to company policy.

Is there any way to do it without the use of addins?
 
Upvote 0
Peter's suggestion seems as good as anything to me.

You seem to suggest that you can apply an autofilter where things do match criteria so is it not as straightforward to apply one where they don't match?
 
Upvote 0
Is there a way to apply a filter that filters all data that does not equal, for example, "Test Data".

I dont know the code sorry.
 
Upvote 0
Is there a way to apply a filter that filters all data that does not equal, for example, "Test Data".

I dont know the code sorry.
Yes, from the AutoFilter drop-down;
1. Choose (Custom)
2. In the top left box choose 'Does not equal'
3. In the top right box choose or type: Test data
 
Upvote 0
As an alternative, here is a possible VBA approach. It assumes the Active Cell is in the filter area. You may not want the last line that turns the AutoFilter off.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DeleteHiddenRows()
    <SPAN style="color:#00007F">Dim</SPAN> FirstRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
    FirstRow = ActiveCell.CurrentRegion.Row + 1
    LastRow = FirstRow + ActiveCell.CurrentRegion.Rows.Count - 2
    
    <SPAN style="color:#00007F">For</SPAN> r = LastRow <SPAN style="color:#00007F">To</SPAN> FirstRow <SPAN style="color:#00007F">Step</SPAN> -1
        <SPAN style="color:#00007F">If</SPAN> Rows(r).EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> Rows(r).EntireRow.Delete
    <SPAN style="color:#00007F">Next</SPAN> r
    
    ActiveSheet.AutoFilterMode = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Hi,

this looks to me as you have multiple columns with different filters and you cannot use "is not..." in your filter

perhaps this will help
Code:
Sub delete_reverse_filter()
'Erik Van Geit
'061009
'delete all hidden rows (hidden by autofilter)

Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count
Columns(1).Insert
    With Range("A2:A" & LR)
    .Value = 1
    ActiveSheet.ShowAllData
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
    End With
Columns(1).Delete

End Sub
it is very simple code
1. insert column
2. put a "1" in each visible cell
3. show all data
4. delete blanks-entirerow (without loop = very quick)
5. delete inserted column

kind regards,
Erik
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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