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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Peter_SSs

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

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175
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
 

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175

ADVERTISEMENT

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?
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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?
 

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175

ADVERTISEMENT

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.
 

Peter_SSs

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

Peter_SSs

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

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Forum statistics

Threads
1,141,626
Messages
5,707,486
Members
421,510
Latest member
haroonstr

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