Needs macros to delete row ranges in excel

Christopher Schene
Christopher Schene used Ask the Experts™
on
I would like to delete a range of rows, but not the top row,  that are missing a string in a certain column.

In this particular question I am looking for all of the latest Android Marshmallow devices where are identified by the string "Linux; Android 6.0.1;" in column W. The row header is "USER_AGENT" and I don't want to delete that one either.  

All other rows are to be deleted.

This is a huge spread sheet (65000 rows+) and I don't want to do this manually.

Thanks,

Chris
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
try this on a copy.

Sub test()
With ActiveSheet
    .AutoFilterMode = False
    With Range("W2", Range("W" & Rows.Count).End(xlUp))
        .AutoFilter 1, "*Linux; Android 6.0.1;*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With
End Sub

Open in new window

Christopher ScheneSystem Engineer/Software Engineer

Author

Commented:
Won't this one delete the top row (the header) which contains the text "USER_AGENT" but not "*Linux; Android 6.0.1;*"?

Sub test()
With ActiveSheet
    .AutoFilterMode = False
    With Range("W2", Range("W" & Rows.Count).End(xlUp))
        .AutoFilter 1, "*Linux; Android 6.0.1;*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With
End Sub
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Please try this....(Before trying this, create a backup of your data)
Sub DeleteRows()
Dim lr As Long
Application.ScreenUpdating = False
lr = ActiveSheet.UsedRange.Rows.Count
With Range("W1:W" & lr)
   .AutoFilter field:=1, Criteria1:="<>*Linux; Android 6.0.1;*"
   If Range("W2:W" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
      Range("W2:W" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
   End If
   .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Professor JMicrosoft Excel Expert
Top Expert 2014
Commented:
i have a mistake in my code above, the below one will do the job please try this,

Sub test()
With ActiveSheet
    .AutoFilterMode = False
    With Range("W1", Range("W" & Rows.Count).End(xlUp))
        .AutoFilter 1, "*Linux; Android 6.0.1;*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With
End Sub

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
@Professor
Sorry, didn't refresh the page before posting. My sincere apologies.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
@Neeraj,
you do not need to apologise, we are friends , at the end of the day what matter is, that user gets the help.  
It happens to me too :-)
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Thanks Professor, so nice of you. :)
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
thanks my friend :)
Christopher ScheneSystem Engineer/Software Engineer

Author

Commented:
It seems like I was not specific enough. My apologies.

I want to delete rows that are missing these two strings in Col W.

USER_AGENT and Linux; Android 6.0.1;

"...  that are missing a string in a certain column."

It seems the solutions thus far delete the rows that contain  Linux; Android 6.0.1;

I want the "NOT" of that operation
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Did you try the code I suggested in Post ID: 41761228?
I think that does what you are trying to achieve.
Christopher ScheneSystem Engineer/Software Engineer

Author

Commented:
This is what I see when I run 41761228Screen-Shot-2016-08-18-at-12.20.25-P.png
Christopher ScheneSystem Engineer/Software Engineer

Author

Commented:
ID: 41761229 actually deleted every line that contained "*Linux; Android 6.0.1;*"in column W.

I didn't mention at first that I am running on a MAC book because I did not think it significant: would these functions run differently on a PC vs MAC?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Well that shouldn't be the case.
The code is assuming that row1 is the header row and the actual data starts from row2.
Can you upload a small sample workbook to work with (max 50 rows) and highlight the cells in col. W that need to be deleted?
Christopher ScheneSystem Engineer/Software Engineer

Author

Commented:
How about if I just extract column W? What is in that column is fairly generic and not proprietary---but most of the 52 columns are proprietary and I would need to obfuscate them.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
That's completely fine.
Just show what's there in col. W and highlight the cells which needs to be deleted.
Christopher ScheneSystem Engineer/Software Engineer

Author

Commented:
This took a while: this is a sheet with 30,000 + rows and 55 columns and I wonder if the size made a difference.

The rows to be deleted are in purple.

49      Mozilla/5.0 (Linux; Android 6.0.1; D6503 Build/23.5.A.1.291; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/52.0.2743.98 Mobile Safari/537.36

this is the portion of the string I care about: "Linux; Android 6.0.1; "

If you can't duplicate this issue with this smaller spread sheet, I may need to upload a larger one.
test.xlsx
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please find the attached and click the button on Test Sheet to run the code and let me know if the code does what you are trying to achieve.
It contains the same code posted in the Post ID: 41761228.
test.xlsm
Christopher ScheneSystem Engineer/Software Engineer

Author

Commented:
Hmmmmm.

This works on my PC but not my Mac Book.

There must some functional difference between Excel on MAC and Excel on my PC.

I'll need to retest all the suggestions on my PC tonight
Christopher ScheneSystem Engineer/Software Engineer

Author

Commented:
I am not sure why, but your solution works perfectly on Excel running on my PC but does not work when running Excel on my Mac Book.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Can you try the following code to see if that works on your Mac also?

Sub DeleteRows()
Dim lr As Long
Application.ScreenUpdating = False
lr = ActiveSheet.UsedRange.Rows.Count
With Range("W1:W" & lr)
   .AutoFilter field:=1, Criteria1:="<>*Linux; Android 6.0.1;*"
   If Range("W1:W" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
      Range("W2:W" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
   End If
   ActiveSheet.ShowAllData
End With
Application.ScreenUpdating = True
End Sub

Open in new window

Christopher ScheneSystem Engineer/Software Engineer

Author

Commented:
It did work on the MAC. What did you change?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Glad to know that.
I replaced line#10 .AutoFilter with ActiveSheet.ShowAllData.
Christopher ScheneSystem Engineer/Software Engineer

Author

Commented:
Thanks! Great work!

Thanks experts!
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You're welcome. Thanks for the feedback.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial