Solved

Needs macros to delete row ranges in excel

Posted on 2016-08-18
24
63 Views
Last Modified: 2016-08-24
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
0
Comment
Question by:cschene
  • 10
  • 10
  • 4
24 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41761196
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

0
 

Author Comment

by:cschene
ID: 41761216
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
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41761228
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

0
 
LVL 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 250 total points
ID: 41761229
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

0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41761232
@Professor
Sorry, didn't refresh the page before posting. My sincere apologies.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41761234
@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 :-)
1
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41761236
Thanks Professor, so nice of you. :)
1
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41761238
thanks my friend :)
0
 

Author Comment

by:cschene
ID: 41761383
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
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41761392
Did you try the code I suggested in Post ID: 41761228?
I think that does what you are trying to achieve.
0
 

Author Comment

by:cschene
ID: 41761479
This is what I see when I run 41761228Screen-Shot-2016-08-18-at-12.20.25-P.png
0
 

Author Comment

by:cschene
ID: 41761568
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?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41761906
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?
0
 

Author Comment

by:cschene
ID: 41761965
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.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41761997
That's completely fine.
Just show what's there in col. W and highlight the cells which needs to be deleted.
0
 

Author Comment

by:cschene
ID: 41765934
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
0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41766324
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
0
 

Author Comment

by:cschene
ID: 41767504
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
0
 

Author Closing Comment

by:cschene
ID: 41768184
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.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41768193
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

0
 

Author Comment

by:cschene
ID: 41768269
It did work on the MAC. What did you change?
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41768285
Glad to know that.
I replaced line#10 .AutoFilter with ActiveSheet.ShowAllData.
0
 

Author Comment

by:cschene
ID: 41768855
Thanks! Great work!

Thanks experts!
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41769084
You're welcome. Thanks for the feedback.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
get curent GPS location 1 27
TT Copy Formula 3 16
Populate data based on a  criteria 2 18
Added a column screws up code 5 17
This article will show you how to use shortcut menus in the Access run-time environment.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now