Delete Rows Based on different Criteria in Multiple Loops

[mrmmickle1]

Hey all. I am trying to delete rows based on several different criteria. I have used a basic format a few times to delete several different criteria. I got this code on the forum about a year ago. To use it I would simply change the criteria and just call a few of these procedures back to back to get the result I wanted. However, I am trying to learn how to do this in a loop all in one procedure to be more efficient. Here is what I have used in the past. I would just substitute out the criteria as needed:

Sub Delete_WO()
Dim lr As Long, i As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
    If Range("A" & i).Value = "W/O#" Then Rows(i).Delete
Next i

End Sub

I have attempted to complete the task but have had no luck. I want to "look" in column C first and find out if the value "Template" exists. If this is the case I want the whole row deleted. THen I want to do the same thing with column B. First with values that are like "BB*" and then with values like "AA*" THese values actually occur like this AA-##### or BB-###### (ex. AA-45678 or BB-54657) Here is what I have currently:

Sub DelTemplate()

Dim  lr    As Long
Dim  i     As Long


lr = Range("C" & Rows.Count).End(xlUp).Row

For i = lr To 1 Step -1
    If Range("C" & i).Value = "Template" Then Rows(i).Delete

Next i

    Set lr = Range("B" & Rows.Count).End(xlup).Row
    If Range("B" & i).Value Like "BB*" Then Rows(i).Delete

Next i
 
    If Range("B" & i).Value Like "AA*" Then Rows(i).Delete

Next i

End Sub

Any help you could provide would be incredible!!! Thank you.

 

[Michael M]

Try this
Keep inmind, when deleting rows..ALways start from the bottom and work up, otherwise you'll find on occasion rows don't get deleted when they should have
Also it's faster !!!

Sub MM1()
Dim lastR As Long, c As Range, rng As Range
Dim delRange As Range, ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
ws.Activate
lastR = WorksheetFunction.Max(Cells(Rows.Count, 2).End(xlUp).Row, Cells(Rows.Count, 3).End(xlUp).Row)
    For ce = lastR To 1 Step -1
        If Range("B" & ce).Value Like "AA*" Or Range("B" & ce).Value Like "BB*" Or Range("B" & ce).Value Like "*52*" Or Range("B" & ce).Offset(, 1).Value = "Template" Then
            Rows(ce).EntireRow.Delete
        End If
    Next ce
Next ws
Application.ScreenUpdating = True
End Sub

 

[mrmmickle1]

Michael,

Thank you so much for the tips. I feel like I have been making some real headway lately due mainly to you and other board regulars. I tried the code as specified and unfortunately I am still unable to get it to run without breaking. I am now getting a compile error: type mismatch on the below line of code

For ce = lastR To 1 Step -1

It looks like the cursor is highlighting the blue portion of the code.

So I also want to understand that portion you mentioned about running the code from the bottom up and it being faster (Or I guess the logic of it all). Why would information occasionally be missed when running procedures from the top down? What makes the code run quicker? Thank you so much for the help you are a lifesaver as well as a valuable source of information!!!

 

[Michael M]

Try

Sub MM1()
Dim lastR As Long, ce as Long, rng As Range
Dim delRange As Range, ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
ws.Activate
lastR = WorksheetFunction.Max(Cells(Rows.Count, 2).End(xlUp).Row, Cells(Rows.Count, 3).End(xlUp).Row)
    For ce = lastR To 1 Step -1
        If Range("B" & ce).Value Like "AA*" Or Range("B" & ce).Value Like "BB*" Or Range("B" & ce).Value Like "*52*" Or Range("B" & ce).Offset(, 1).Value = "Template" Then
            Rows(ce).EntireRow.Delete
        End If
    Next ce
Next ws
Application.ScreenUpdating = True
End Sub

 

[mrmmickle1]

Try

Sub MM1()
Dim lastR As Long, ce as Long, rng As Range
Dim delRange As Range, ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
ws.Activate
lastR = WorksheetFunction.Max(Cells(Rows.Count, 2).End(xlUp).Row, Cells(Rows.Count, 3).End(xlUp).Row)
    For ce = lastR To 1 Step -1
        If Range("B" & ce).Value Like "AA*" Or Range("B" & ce).Value Like "BB*" Or Range("B" & ce).Value Like "*52*" Or Range("B" & ce).Offset(, 1).Value = "Template" Then
            Rows(ce).EntireRow.Delete
        End If
    Next ce
Next ws
Application.ScreenUpdating = True
End Sub

Success!!! Your the man. I appreciate the diligent effort!

 

[Michael M]

pleasure...

 

[brian.wethington]

Try this
Keep inmind, when deleting rows..ALways start from the bottom and work up, otherwise you'll find on occasion rows don't get deleted when they should have
Also it's faster !!!

Just a few notes about deleting rows. It is normally more efficient to only delete 1 time than to delete several times within a loop. Also in doing this you don't have to loop from the bottom. 1 last point, if you use a each cell method instead of a integer/long method you don't need to worry about looping backwards as well as it it will hit each cell regardless.

Just some food for thought.

Sub DeleteLoop()
Dim lastR As Long
Dim ce As Range, rng As Range, delRange As Range
Dim Sh As Worksheet

For Each Sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
    lastR = WorksheetFunction.Max(Sh.Cells(Rows.Count, 2).End(xlUp).Row, Sh.Cells(Rows.Count, 3).End(xlUp).Row)
    Set rng = Sh.Range("B1:B" & lastR)
    For Each ce In rng
        If ce.Value Like "BB*" Or ce.Value Like "AA*" Or ce.Offset(, 1).Value = "Template" Then
            If delRange Is Nothing Then
                Set delRange = ce.EntireRow
            Else
                Set delRange = Union(delRange, ce.EntireRow)
            End If
        End If
    Next ce
    If Not delRange Is Nothing Then
        delRange.Delete
        Set delRange = Nothing
    End If
Next Sh
End Sub

 

[Michael M]

All valid points Brian...I didn't want to go the Autofilter / non loop way, in case it created further confusion for Mr.Mickle

 

[brian.wethington]

All valid points Brian...I didn't want to go the Autofilter / non loop way, in case it created further confusion for Mr.Mickle

Interesting, my original code avoided autofilter because I wasn't certain about the OP's needs on type of case matching exactly so I tried to match his methodology in that regard. Now that I think about it I don't know if that was a valid concern or not. I do generally prefer the autofilter method as well.

 

[mrmmickle1]

Interesting, my original code avoided autofilter because I wasn't certain about the OP's needs on type of case matching exactly so I tried to match his methodology in that regard. Now that I think about it I don't know if that was a valid concern or not. I do generally prefer the autofilter method as well.

I appreciate the help from both you and Michael. I have been trying to glean alot of information from these boards lately and Message Board regulars who are willing to not only post solution but take the time to explain them are incredible resources. And Michael may very well be right about confusing me. My knowledge is rudimentary at best..... While, I am always trying to further my knowledge sometimes it takes me more then one example. Thank you both so much for the help. I will keep these points in mind and try to apply them as I move forward with different VBA tasks.

 

[Michael M]

This may help....it is a list of VBA sites and training aids kindly compiled by Hiker95
Have a browse through, there is some great stuff in there for VBA

MrExcel's Products: Books, CDs, Podcasts Discuss topics related to Holy Macro! Products: Learn Excel from MrExcel, VBA and Macros for Microsoft Excel,Holy Macro! It's 2500 VBA Examples CD, Guerilla Data Analysis Using Microsoft Excel and Excel Knowledge Base CD and the MrExcel Podcasts.
http://www.mrexcel.com/forum/forumdisplay.php?f=19

There are over 1800 Excel videos/tutorials here:
http://www.youtube.com/user/ExcelIsFun

Getting Started with VBA. 
http://www.datapigtechnologies.com/ExcelMain.htm

If you are serious about learning VBA try 
http://www.add-ins.com/vbhelp.htm

Excel Tutorials and Tips - VBA - macros - training
http://www.mrexcel.com/articles.shtml

Here's a good primer on the scope of variables.
Scope Of Variables And Procedures

See David McRitchie's site if you just started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

What is a Visual Basic Module?
http://www.emagenit.com/VBA%20Folder...vba_module.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

Creating An XLA Add-In For Excel, Writing User Defined Functions In VBA
http://www.cpearson.com/excel/createaddin.aspx

How do I create a PERSONAL.XLS(B) or Add-in
http://www.rondebruin.nl/personal.htm

Creating custom functions
http://office.microsoft.com/en-us/ex...117011033.aspx

Writing Your First VBA Function in Excel
http://www.exceltip.com/st/Writing_Y...Excel/631.html

VBA for Excel (Macros)
http://www.excel-vba.com/excel-vba-contents.htm

VBA Lesson 11: VBA Code General Tips and General Vocabulary
http://www.excel-vba.com/vba-code-2-1-tips.htm

Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

Learn to debug: 
http://www.cpearson.com/excel/debug.htm

How To: Assign a Macro to a Button or Shape
http://peltiertech.com/WordPress/how...tton-or-shape/

User Form Creation
http://www.contextures.com/xlUserForm01.html

When To Use a UserForm & What to Use a UserForm For
http://www.ozgrid.com/Excel/free-tra...ba2lesson2.htm

Excel Tutorials / Video Tutorials - Functions
http://www.contextures.com/xlFunctions02.html

INDEX MATCH - Excel Index Function and Excel Match Function
http://www.contextures.com/xlFunctions03.html

Excel Data Validation
http://www.contextures.com/xlDataVal08.html#Larger
http://www.contextures.com/excel-dat...ation-add.html

Your Quick Reference to Microsoft Excel Solutions
http://www.xl-central.com/index.html

New! Excel Recorded Webinars
http://www.datapigtechnologies.com/ExcelMain.htm

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
http://www.cpearson.com/Excel/vbe.aspx

VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…

by Stephen/ Bovey, Rob/ Green, John Bullen (Paperback - Feb 11, 2005)
Professional Excel Development

by Rob Bovey, Stephen Bullen, John Green, and Robert Rosenberg (Paperback - Sep 26, 2001)
Excel 2002 VBA: Programmers Reference

DonkeyOte: My Recommended Reading, Volatility
http://www.decisionmodels.com/calcsecretsi.htm

Sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Arrays
http://www.xtremevbtalk.com/showthread.php?t=296012
http://www.vbtutor.net/vba/vba_chp21.htm

Pivot Intro
http://peltiertech.com/Excel/Pivots/pivotstart.htm

Email from XL - VBA
http://www.rondebruin.nl/sendmail.htm

Outlook VBA
http://www.outlookcode.com/article.aspx?ID=40

Function Dictionary
http://www.xlfdic.com/

Function Translations
http://www.piuha.fi/excel-function-name-translation/

Dynamic Named Ranges
http://www.contextures.com/xlNames01.html

How to create Excel Dashboards
http://www.mrexcel.com/Excel-dashboards-Xcelsius.html
http://www.contextures.com/excel-dashboards.html
http://chandoo.org/wp/excel-dashboards/
http://chandoo.org/wp/management-dashboards-excel/
http://www.exceldashboardwidgets.com/
http://www.andypope.info/charts/gauge.htm 

Excel Dashboard / Scorecard Ebook
http://www.qimacros.com/excel-dashboard-scorecard.html

Mike Alexander from Data Pig Technologies
Excel 2007 Dashboards & Reports For Dummies

Templates
http://www.cpearson.com/Excel/Topic.aspx
http://www.contextures.com/excel-tem...lf-scores.html

Date & Time stamping:
http://www.mcgimpsey.com/excel/timestamp.html

Get Formula / Formats thru custom functions:
http://dmcritchie.mvps.org/excel/formula.htm#GetFormat

A nice informative MS article "Improving Performance in Excel 2007"
http://msdn.microsoft.com/en-us/library/aa730921.aspx

Progress Meters
http://www.andypope.info/vba/pmeter.htm
http://www.xcelfiles.com/ProgressBar.html

And, as your skills increase, try answering posts on sites like:
http://www.mrexcel.com
http://www.excelforum.com
http://www.ozgrid.com
http://www.vbaexpress.com
http://www.excelfox.com