multiple row deletion with one macro or vlookup?

rhybeka

New Member
Joined
Mar 22, 2013
Messages
32
Hi all,

So I have a macro that removes unnecessary rows of data via a user prompt (probably snagged from a thread on here!), but I’d prefer to list the criteria of the items I need to remove in the macro since the criteria will not change. I think my biggest hurdle is my criteria data is in two different columns, not one. I.e. I have rows that have a type of "exam" or "web", once those are removed, I need to remove rows with a code that ends in a number (100,110,120, etc). I’ve thought of putting my criteria into a vlookup and leveraging an if statement but I just wasn’t sure which way is better to go about it. Any constructive input appreciated - thanks :)



Code:
Sub DeleteRows()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    Dim c As Range
    Dim SrchRng As Range
    Dim SrchStr As String
     
    Set SrchRng = ActiveSheet.Range("myData", ActiveSheet.Range("A65536").End(xlUp))
    SrchStr = InputBox("Please Enter A Search String")
    Do
        Set c = SrchRng.Find(SrchStr, LookIn:=xlValues)
        If Not c Is Nothing Then c.EntireRow.Delete
    Loop While Not c Is Nothing
    
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
If Err.Number <> 0 Then MsgBox "Errors occurred"


End Sub
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Can you post some sample data so we can see where things are? What are the two columns to look for in the criteria? Are there more text strings besides "exam" and "web" you need to look for? Are there more "last 3 digits of code" you need to look for besides those 3? Which of the previous 2 items are in which columns?

That code is pretty much looking at the entire sheet. If I were you, I'd look in the specific columns. There's many ways to do that.

.....

Not knowing this information, I can only take guesses, so I set up my own dummy data that looks like this...

Excel Workbook
ABCDE
1Dummy1ExamThisCode110Should delete
2Dummy2Webshouldcode120Should delete
3dummy3FreddeleteCode130Not Del
4dummy4Deadthecode140Not Del
5dummy5Weblinescode120Should delete
6dummy6Examthatcode150Not Del
7dummy7ExamwasCode110Should delete
8dummy8Fredrequestedcode110Not Del
9dummy9Deadbycode110Not Del
10dummy10Webyoucode120Should delete
Sheet3


With that dummy data I wrote this, which does as you ask, however, this may not cover where your data is located, however you might be able to use it to figure out how to set yours up.

Code:
Sub DelRowCrit()

Dim i As Long
Dim lr As Long

lr = Range("a" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
    If Right(Cells(i, "D").Value, 3) = "110" Or Right(Cells(i, "D").Value, 3) = "120" Or Right(Cells(i, "D").Value, 3) = "130" Then
        If LCase(Cells(i, "B")) = "exam" Or LCase(Cells(i, "B")) = "web" Then
            Rows(i).Delete
        End If
    End If
Next i

End Sub

if this doesn't work, post some dummy data and we'll see if we can't get you fixed up.
 
Upvote 0
I’ve thought of putting my criteria into a vlookup and leveraging an if statement but I just wasn’t sure which way is better to go about it. Any constructive input appreciated - thanks :)

I don't think vlookup would be an option in this case. Use vlookup only when the desired result is the output of the lookup. Maybe there are other uses for it, but nothing I would personally ever use.

Like with the above code, all you have to do is run the criteria through 2 different if statements. The first one covers the first criteria, the second if statement covers the 2nd criteria, but only if the first already matches, thus covering both sets of criteria.

and then, of course, you're just looping through the rows starting at the last row going to the first row.
 
Upvote 0
Thanks for the help, RJ! I think that piece of code you provided may get me close. I also realized for the large grouping of lines I need to replace, I can use the training type column as well. So I just need to code it to remove three training code types and one training title contains. Still has to look at two columns though.

Assignment Type
User Id
Last Name
First Name
Complete By
Curriculum
Training Code
Training Title
Course Version
Business Unit
Department
Job Function
Manager Name
Work Location
Creation Date
Completion Dates
Days On To-Do List
Effective Date
Class Code
Start Time
Training Type
Required
john.doe
doe
john
1/4/2013 08:33:04 PM
AB-100
Training
1
Whirlygigs
Thingamabobs
QA
jane smith
North Pole
1/4/2013 08:33:04 PM
3/13/2009
0
11/2/2008
1
AgnWB
Required
john.doe
doe
john
1/4/2013 08:33:04 PM
AB-101
Training
1
Whirlygigs
Thingamabobs
QA
jane smith
North Pole
1/4/2013 08:33:04 PM
11/5/2009
0
11/2/2008
2
AgnWB
Required
john.doe
doe
john
1/7/2013 01:05:04 PM
AB-101-Pretest
Training Pretest
1
Whirlygigs
Thingamabobs
QA
jane smith
North Pole
1/7/2013 01:05:04 PM
11/5/2009
0
11/2/2008
2
AgnQz
Required
john.doe
doe
john
1/7/2013 01:05:04 PM
AB-099-Pretest
Training Posttest
2
Whirlygigs
Thingamabobs
QA
jane smith
North Pole
1/7/2013 01:05:04 PM
11/5/2010
1
11/2/2009
3
AgnQz
Required
jane.smith
smith
jane
5/7/2012 11:59:59 PM
AB-111-EXAM
Training
1
Whirlygigs
Thingamabobs
QA
bob brown
North Pole
4/26/2012 03:41:23 PM
4/28/2012 12:26:29 PM
2
4/26/2012
1
Exam
Required
jane.smith
smith
jane
5/7/2012 11:59:59 PM
AB-113-EXAM
Training Posttest
1
Whirlygigs
Thingamabobs
QA
bob brown
North Pole
4/26/2012 03:33:26 PM
4/28/2012 12:08:29 PM
2
4/26/2012
1
Exam
Required
jane.smith
smith
jane
5/7/2012 11:59:59 PM
DEF-100
Training
2
Whirlygigs
Thingamabobs
QA
bob brown
North Pole
4/26/2012 03:41:23 PM
4/28/2012 12:24:24 PM
2
4/26/2012
1
Web
Required
jane.smith
smith
jane
5/7/2012 11:59:59 PM
GHI-100
Training
2
Whirlygigs
Thingamabobs
QA
bob brown
North Pole
4/26/2012 03:33:26 PM
4/28/2012 12:05:49 PM
2
4/26/2012
1
Web

<TBODY>
</TBODY>

<TBODY>
</TBODY>


Names have been changed to protect the innocent :) basically, I can remove anything with a training type of Web, Exam, or AgnWB, but I need to remove the Pretests as well, and they share a training type of AgnQz with the Posttests. I have another macro that creates the sheet into a dynamic range called myData since I'm trying to make this into a template so that all I have to do is open the file, put the data into the tab and run the macro. After the rows are removed, I need to sort the data by work location alphabetically so the sheet can be copied and sub totaled for a chart that goes out in an email with this data attached and a pivot table....so this macro is only part of the puzzle :) Thanks again RJ!
 
Upvote 0
I think I got what you need and this will at least get you part of the way there. Verify the Before Code and After code behavior is accurate. I used the empty Curriculum column as a guide for what should and shouldn't be deleted. Just to have something visual.

Excel Workbook
ABCDEFGHIJKLMNOPQRSTU
1Assignment TypeUser IdLast NameFirst NameComplete ByCurriculumTraining CodeTraining TitleCourse VersionBusiness UnitDepartmentJob FunctionManager NameWork LocationCreation DateCompletion DatesDays On To-Do ListEffective DateClass CodeStart TimeTraining Type
2Requiredjohn.doedoejohn1/4/2013 20:33DELETEAB-100Training1WhirlygigsThingamabobsQAjane smithNorth Pole1/4/2013 20:333/13/2009011/2/20081AgnWB
3Requiredjohn.doedoejohn1/4/2013 20:33DELETEAB-101Training1WhirlygigsThingamabobsQAjane smithSouth Pole1/4/2013 20:3311/5/2009011/2/20082AgnWB
4Requiredjohn.doedoejohn1/7/2013 13:05DELETEAB-101-PretestTraining Pretest1WhirlygigsThingamabobsQAjane smithUSA1/7/2013 13:0511/5/2009011/2/20082AgnQz
5Requiredjohn.doedoejohn1/7/2013 13:05AB-099-PretestTraining Posttest2WhirlygigsThingamabobsQAjane smithAustrailia1/7/2013 13:0511/5/2010111/2/20093AgnQz
6Requiredjane.smithsmithjane5/7/2012 23:59DELETEAB-111-EXAMTraining1WhirlygigsThingamabobsQAbob brownGreat Brittian4/26/2012 15:414/28/2012 12:2624/26/20121Exam
7Requiredjane.smithsmithjane5/7/2012 23:59DELETEAB-113-EXAMTraining Posttest1WhirlygigsThingamabobsQAbob brownChile4/26/2012 15:334/28/2012 12:0824/26/20121Exam
8Requiredjane.smithsmithjane5/7/2012 23:59DELETEDEF-100Training2WhirlygigsThingamabobsQAbob brownGermany4/26/2012 15:414/28/2012 12:2424/26/20121Web
9Requiredjane.smithsmithjane5/7/2012 23:59DELETEGHI-100Training2WhirlygigsThingamabobsQAbob brownFrance4/26/2012 15:334/28/2012 12:0524/26/20121Web
10Requiredjohn.doedoejohn1/4/2013 20:33DELETEAB-100Training1WhirlygigsThingamabobsQAjane smithSpain1/4/2013 20:333/13/2009011/2/20081AgnWB
11Requiredjohn.doedoejohn1/4/2013 20:33DELETEAB-101Training1WhirlygigsThingamabobsQAjane smithUSA1/4/2013 20:3311/5/2009011/2/20082AgnWB
12Requiredjohn.doedoejohn1/7/2013 13:05DELETEAB-101-PretestTraining Pretest1WhirlygigsThingamabobsQAjane smithAustrailia1/7/2013 13:0511/5/2009011/2/20082AgnQz
13Requiredjohn.doedoejohn1/7/2013 13:05AB-099-PretestTraining Posttest2WhirlygigsThingamabobsQAjane smithGreat Brittian1/7/2013 13:0511/5/2010111/2/20093AgnQz
14Requiredjane.smithsmithjane5/7/2012 23:59AB-111-EXAMTraining1WhirlygigsThingamabobsQAbob brownChile4/26/2012 15:414/28/2012 12:2624/26/20121AgnQz
15Requiredjane.smithsmithjane5/7/2012 23:59AB-113-EXAMTraining Posttest1WhirlygigsThingamabobsQAbob brownGermany4/26/2012 15:334/28/2012 12:0824/26/20121AgnQz
16Requiredjane.smithsmithjane5/7/2012 23:59DELETEDEF-100Training2WhirlygigsThingamabobsQAbob brownFrance4/26/2012 15:414/28/2012 12:2424/26/20121Web
17Requiredjane.smithsmithjane5/7/2012 23:59GHI-100Training2WhirlygigsThingamabobsQAbob brownSpain4/26/2012 15:334/28/2012 12:0524/26/20121AgnQz
Before Code


Excel Workbook
ABCDEFGHIJKLMNOPQRSTU
1Assignment TypeUser IdLast NameFirst NameComplete ByCurriculumTraining CodeTraining TitleCourse VersionBusiness UnitDepartmentJob FunctionManager NameWork LocationCreation DateCompletion DatesDays On To-Do ListEffective DateClass CodeStart TimeTraining Type
2Requiredjohn.doedoejohn1/7/2013 13:05AB-099-PretestTraining Posttest2WhirlygigsThingamabobsQAjane smithAustrailia1/7/2013 13:0511/5/2010111/2/20093AgnQz
3Requiredjane.smithsmithjane5/7/2012 23:59AB-111-EXAMTraining1WhirlygigsThingamabobsQAbob brownChile4/26/2012 15:414/28/2012 12:2624/26/20121AgnQz
4Requiredjane.smithsmithjane5/7/2012 23:59AB-113-EXAMTraining Posttest1WhirlygigsThingamabobsQAbob brownGermany4/26/2012 15:334/28/2012 12:0824/26/20121AgnQz
5Requiredjohn.doedoejohn1/7/2013 13:05AB-099-PretestTraining Posttest2WhirlygigsThingamabobsQAjane smithGreat Brittian1/7/2013 13:0511/5/2010111/2/20093AgnQz
6Requiredjane.smithsmithjane5/7/2012 23:59GHI-100Training2WhirlygigsThingamabobsQAbob brownSpain4/26/2012 15:334/28/2012 12:0524/26/20121AgnQz
After Code


Modified code:
Code:
Dim i As Long
Dim lr As Long

'Delete rows not needed
lr = Range("a" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
    If (LCase(Cells(i, "U")) = "exam" Or LCase(Cells(i, "U")) = "web" Or LCase(Cells(i, "U")) = "agnwb") Or LCase(Right(Cells(i, "H").Value, 7)) = "pretest" Then
            Rows(i).Delete
    End If
Next i

'sort Ascending on Column N.
lr = Range("a" & Rows.Count).End(xlUp).Row
Range("A1", "U" & lr).Sort Key1:=Range("N2"), _
                        Order1:=xlAscending, _
                        Header:=xlGuess, _
                   OrderCustom:=1, _
                     MatchCase:=False, _
                   Orientation:=xlTopToBottom, _
                   DataOption1:=xlSortNormal

To cover the additional case of excluding pretests regardless of the training type, I had to put all the IF checks on a single line.
I also added in the sort code. I'm not sure what you're needing to sum (subtotal) or where you want it copied off to, so didn't mess with either of those.

As far as Charts and pivot tables. Good luck. They both frustrate me. If you need assistance with those, you might want to start a new topic with a subject pertaining to the one you need assitance with.
 
Upvote 0
Well Poo, Excel Jeanie doesn't have scroll bars. *sigh* I'll have to check out another tool, but the code should still do the trick for you. Check it out and let me know. Be sure to use on a backup first as it does delete rows of data.
 
Upvote 0
Thanks RJ! any help to get me started is MUCH appreciated. I don't mind doing the pivots by hand atm - considering I'm not even sure anyone uses it to begin with. :p I have another workbook that's a bit larger that is a monthly report that I'll be trying to automate as well and this code will be helpful to use with that as well since the data is somewhat the same :)
 
Upvote 0
just fyi - it works fine for me :) I changed the column it sorts on and added in a line for the screen to not update until it's done. Screen flickering is a tad annoying :) Thanks again!
 
Upvote 0
Thanks RJ! any help to get me started is MUCH appreciated. I don't mind doing the pivots by hand atm - considering I'm not even sure anyone uses it to begin with. :p I have another workbook that's a bit larger that is a monthly report that I'll be trying to automate as well and this code will be helpful to use with that as well since the data is somewhat the same :)

Glad everything worked out. You could do everything with a macro if you wanted to totally automate things. I love macros for automation. It goes Boom Boom done. :) I just haven't had any luck with charts or pivot tables, so I come up with other creative ways to display what is important data to see at a glance and just have the macro do it for me. Other people love their charts & pivot tables. I'm sure there someone here familiar with them if you wanted to automate them too.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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