[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

excel VBA module delete entire rows based on passed in condition

Posted on 2012-09-19
6
Medium Priority
?
569 Views
Last Modified: 2012-09-24
I would like a module that I can pass in a condition. In other words pass into the module the character string im looking for then delete the entire row it exists on.
0
Comment
Question by:lucasd1973
6 Comments
 
LVL 13

Expert Comment

by:Shanan212
ID: 38413801
Sub sample()
    
    Dim x As String, CurrCell As Range, y As Long
    x = "sample"
    

     Range("A1").Select
     Set CurrCell = Cells.Find(What:=x, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

        If Not CurrCell Is Nothing Then
             Rows(CurrCell.Row & ":" & CurrCell.Row).Select
             Selection.Delete Shift:=xlUp
        End If
      Range("A1").Select
End Sub

Open in new window

0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 38413819
These are two amazing references that will show you several ways to do that.

http://www.xtremevbtalk.com/showpost.php?p=1305670&postcount=4

http://www.rondebruin.nl/delete.htm
0
 
LVL 35

Expert Comment

by:Norie
ID: 38414197
Try this.
Sub DeleteMatch(strTerm As String)
Dim rw As Range
Dim rngFnd As Range
Dim I As Long

    For I = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        Set rw = Rows(I)
         Set rngFnd = rw.Find(What:=strTerm)
         If Not rngFnd Is Nothing Then
                rw.Delete
         End If
     Next I
     
End Sub

Open in new window

You would call it like this.
' remove all rows with 'Test' in them

DeleteMatch "Test"

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 18

Accepted Solution

by:
krishnakrkc earned 2000 total points
ID: 38414551
Hi,

Sub DeleteRows(ByRef SearchRange As Range, ByVal DeleteString)
    
    Dim k, i As Long, txt As String
    
    k = SearchRange.Value2
    If TypeOf DeleteString Is Range Then DeleteString = DeleteString.Value2
    
    DeleteString = LCase(DeleteString)
    
    Application.ScreenUpdating = 0
    
    With SearchRange
        For i = UBound(k, 1) To 1 Step -1
            If LCase(k(i, 1)) = DeleteString Then
                txt = txt & ",A" & i
                If Len(txt) > 245 Then
                    txt = Mid$(txt, 2)
                    .Range(txt).EntireRow.Delete
                    txt = vbNullString
                End If
            End If
        Next
        If Len(txt) > 1 Then
            .Range(Mid(txt, 2)).EntireRow.Delete
        End If
    End With
    Application.ScreenUpdating = 1

End Sub

Sub kTest()
    
    DeleteRows Range("Sheet2!A2:A10000"), "ZSP"

End Sub

Open in new window


Kris
0
 

Author Comment

by:lucasd1973
ID: 38414897
Got a run-time error '1004':
Method 'Range' of object'Global'failed
0
 
LVL 35

Expert Comment

by:Norie
ID: 38414906
With which code?
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

831 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