Delete unwanted rows based on value in cells

M

mlarson

Well-known Member
Joined
Aug 25, 2011
Messages
509
Office Version
  1. 2010
Platform
  1. Windows
I am aware this is probably very simple, but I haven't been able to find a usable macro yet. I would like the data in the workbook that is not "Tuesday" (column L) and is not "3" or "4" (column I) to be deleted. All rows that have "Tuesday" and either "3" or "4" should not be deleted. Each row that is not deleted must have Tuesday in column L. All other cells are blank intentionally. The workbook will remain the same titled WKBK1 (no need to put the output data in a separate workbook nor separate spreadsheet).

Do you know what the macro would be so I don't have to copy and paste a formula over a large number of cells? Thanks.

WKBK1
<table border="0" cellpadding="0" cellspacing="0" width="777"><col style="width: 48pt;" width="64"> <col style="width: 55pt;" width="73"> <col style="width: 48pt;" width="64" span="10"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" width="64" height="20">A</td> <td class="xl63" style="width: 55pt;" width="73">B</td> <td class="xl63" style="width: 48pt;" width="64">C</td> <td class="xl63" style="width: 48pt;" width="64">D</td> <td class="xl63" style="width: 48pt;" width="64">E</td> <td class="xl63" style="width: 48pt;" width="64">F</td> <td class="xl63" style="width: 48pt;" width="64">G</td> <td class="xl63" style="width: 48pt;" width="64">H</td> <td class="xl63" style="width: 48pt;" width="64">I</td> <td class="xl63" style="width: 48pt;" width="64">J</td> <td class="xl63" style="width: 48pt;" width="64">K</td> <td class="xl63" style="width: 48pt;" width="64">L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2</td> <td class="xl64">
</td> <td class="xl65">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">3</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">Tuesday</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">3</td> <td class="xl64">
</td> <td class="xl65">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">3</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">Tuesday</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">4</td> <td class="xl64">
</td> <td class="xl65">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">1</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">Tuesday</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">5</td> <td class="xl64">
</td> <td class="xl65">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">1</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">Tuesday</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">6</td> <td class="xl64">
</td> <td class="xl65">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">4</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">Tuesday</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">7</td> <td class="xl64">
</td> <td class="xl65">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">4</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">Tuesday</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">8</td> <td class="xl64">
</td> <td class="xl65">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">7</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">Tuesday</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">9</td> <td class="xl64">
</td> <td class="xl65">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">7</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">Tuesday</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">10</td> <td class="xl64">
</td> <td class="xl65">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">7</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">Tuesday</td> </tr> </tbody></table>
WKBK1 (output)
 <table border="0" cellpadding="0" cellspacing="0" width="777"><col style="width: 48pt;" width="64"> <col style="width: 55pt;" width="73"> <col style="width: 48pt;" width="64" span="10"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">A</td> <td class="xl65" style="width: 55pt;" width="73">B</td> <td class="xl65" style="width: 48pt;" width="64">C</td> <td class="xl65" style="width: 48pt;" width="64">D</td> <td class="xl65" style="width: 48pt;" width="64">E</td> <td class="xl65" style="width: 48pt;" width="64">F</td> <td class="xl65" style="width: 48pt;" width="64">G</td> <td class="xl65" style="width: 48pt;" width="64">H</td> <td class="xl65" style="width: 48pt;" width="64">I</td> <td class="xl65" style="width: 48pt;" width="64">J</td> <td class="xl65" style="width: 48pt;" width="64">K</td> <td class="xl65" style="width: 48pt;" width="64">L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2</td> <td class="xl66">
</td> <td class="xl67">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">3</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">Tuesday</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3</td> <td class="xl66">
</td> <td class="xl67">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">3</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">Tuesday</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">4</td> <td class="xl66">
</td> <td class="xl67">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">4</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">Tuesday</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">5</td> <td class="xl66">
</td> <td class="xl67">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">4</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">Tuesday</td> </tr> </tbody></table>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
I think this will do what you want:
Code: 
Sub MyDeleteRows()
 
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim j As Long
    
    Application.ScreenUpdating = False
    
'   Designate first data row here
    myFirstRow = 2
    
'   Calculate last row with data using column L
    myLastRow = Cells(Rows.Count, "L").End(xlUp).Row
    
'   Loop through all rows backwards and delete specified rows
    For j = myLastRow To myFirstRow Step -1
        If Cells(j, "L") = "Tuesday" And (Cells(j, "I") = 3 Or Cells(j, "I") = 4) Then
    
        Else
            Rows(j).EntireRow.Delete
        End If
    Next j
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Joe, thanks for that macro. It worked for a small sample size (about 2,000 rows) but I have a spreadsheet of 1,000,000 rows and it did not work. It seemed to be an endless loop and I let it run for 10 minutes but it never finished. Any suggestions on getting it to work for the larger data set? Thanks.
 
Upvote 0
Is your last possible row on your spreadsheet populated with data (row # 1048576)?

I don't think it is caught in an endless loop, I think it is just taking a real long time to run because of the size of your data. If you stop the macro, and view your data, I bet you will see that it had deleted a bunch of rows, you just aren't letting it finish.

Unfortunately, looping through large sets of data is not the most efficient process. Perhaps created a calculation (i.e. IF statement) on each row of your Excel spreadsheet, returning the words "KEEP" or "DELETE" and filtering on this field would be more efficient.

However, I would also be inclined to say that if you are working with large data sets over a million records long, Excel may not be the best tool to use. You may be venturing into database territory (Access, SQL, Oracle, etc).
 
Upvote 0
The last value is in row 1,048,000 so it's close to the end but doesn't use the maximum number of rows allowed.

I like the idea of the IF statement returning "DELETE" or "KEEP." Do you mind sharing what that macro would look like? And what a second macro would look like to delete those rows that read "DELETE"? I'm not very well-versed creating macros but I like this idea.

Thanks a ton for your help on this!
 
Upvote 0
Well, the beauty of it is that you don't really need to use a macro (unless you are looking to re-use the process and automate for future use).

You would just creating the formula in the first cell, double-click on the lower right-hand corner of the cell to autofill for all rows, then apply Excel's built-in Filtering functionality.

If you do need to automate it via a macro, you can get most of the code you need by using the Macro Recorder and recording yourself performing the steps yourself. Then you would probably just need to make a few tweaks to the code to make the range references "dynamic". We can help with that part, if you post the code you record.
 
Upvote 0
Joe, that is a great suggestion. The code I came up with is below. You mentioned I could make it dynamic instead of setting a specific end cell?

Code: 
Sub KeepDeleteMacro()
'
' KeepDeleteMacro Macro
'

'
    Windows("WKBK1.xlsm").Activate
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(AND(RC[-1]=""Tuesday"",RC[-4]=3),AND(RC[-1]=""Tuesday"",RC[-4]=4)),""KEEP"",""DELETE"")"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L1048000")
    Range("L2:L1048000").Select
    Range("M2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$L$1048000").AutoFilter Field:=12, Criteria1:= _
        "KEEP"
   
End Sub
 
Upvote 0
Yep, we can calculate the last row, then place that variable in all references to it, like this:
Code: 
Sub KeepDeleteMacro()
'
' KeepDeleteMacro Macro
'
    Dim myLastRow As Long
    
'   Find last row with data in column L
    If Len(Cells(Rows.Count, "L")) > 0 Then
        myLastRow = Rows.Count
    Else
        myLastRow = Cells(Rows.Count, "L").End(xlUp).Row
    End If
'
    Windows("WKBK1.xlsm").Activate
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(AND(RC[-1]=""Tuesday"",RC[-4]=3),AND(RC[-1]=""Tuesday"",RC[-4]=4)),""KEEP"",""DELETE"")"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L" & myLastRow)
    Range("L2:L" & myLastRow).Select
    Range("M2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$L$" & myLastRow).AutoFilter Field:=12, Criteria1:= _
        "KEEP"
   
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
D
  • Solved
setting cell value to 1 based on 2 conditions
2
Replies
13
Views
175
Peter_SSs
Peter_SSs
Seafox
  • Question Question
Glide Path Calculation
Replies
3
Views
187
RobP
R
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
J
  • Solved
How to get the Range of Cells with "Values" within a Range of formulas
Replies
4
Views
374
jrtraylor
J

Forum statistics

Threads
1,224,590
Messages
6,179,758
Members
452,940
Latest member
rootytrip

Share this page

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