I have a macro that filters
I need to delete all rows NOT filtered, I have not been able to find or come up with anything on how to do this
Thanks
I have a macro that filters
I need to delete all rows NOT filtered, I have not been able to find or come up with anything on how to do this
Thanks
Maybe not what you ask, but it could be a workaround.
Set the filter on the rows you want to delete.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
This is assuming that the data is all in a table format and that the table starts at Row 1. It also keeps the headers.![]()
Range("A1").CurrentRegion.Select Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1, Selection.Columns.Count).Select Selection.EntireRow.Delete
Thank you greggetersen75, that was what I needed.
I am filtering on all the duplicates and deleting the unique rows this is fast and simple way to do this.
I am open to other suggestions if you have them.
Last edited by capson; 05-06-2014 at 09:53 AM.
@oeldere,
That is thinking outside the square, what a really simple suggestion that I imagine would be simple to record/develop
Well done
Jmac
Cheers
Jmac1947
1. Please consider clicking on the * Add Reputation if you think this post has helped you
2. Mark your thread as SOLVED when question is resolved
Well I am all into reclyling code. So I often use sub routines over and over again and name ranges for fields, So for this example, let's say I have a worksheet called "Data", a field named "Project", and I am trying to delete a project called "T3-1Test"
This is a more sophisticated solution, but really reusable.![]()
Sub DeleteRangeSample() Call RangeName(Worksheets("Data")) Call DeleteRange(Worksheets("Data"), "Project", "T3-1Test") End Sub Private Sub RangeName(xlWs As Excel.Worksheet) 'Add Range Names for Reference Application.DisplayAlerts = False xlWs.Range("A1").CurrentRegion.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False '########## changed Application.DisplayAlerts = True End Sub Private Sub DeleteRange(xlWs As Excel.Worksheet, strRangeName As String, varTypeNumText As Variant) Dim xlCell As Excel.Range With xlWs Set xlCell = .Range(strRangeName).Find(What:=varTypeNumText, LookIn:=xlValues, Lookat:=xlWhole) If Not xlCell Is Nothing Then If .AutoFilterMode = True Then .AutoFilterMode = False .Cells(1, 1).CurrentRegion.AutoFilter Field:=Range(strRangeName).Column, Criteria1:=varTypeNumText, Operator:=xlAnd .Range(strRangeName).EntireRow.Delete .AutoFilterMode = False End If End With End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks