• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 118
  • Last Modified:

Delete row if not contains certain text

I wanted to record this but I don't think Excel can record conditionals, i.e. IF, ELSE, etc. So, I thought this may be easy for an Excel VB expert.

I need a Excel macro that will...
- Search whole worksheet and will delete a row if the Project Title cell...
- does not end with string: / total Total Fee
- and does not start with at least a minimum length 4 character number string. These pass: 2100, 9812
- and those same strings may also be like: 210012, 210012A, 210012A1, 98123D

I've attached a sample spreadsheet.
DelRowIfNotProject.xlsx
0
NVIT
Asked:
NVIT
  • 3
  • 2
  • 2
2 Solutions
 
Wayne Taylor (webtubbs)Commented:
Try this macro...

Sub DeleteRows()

    Dim DeleteRange As Range, rw As Long
    Const EndsWith As String = "/ Total Total Fee"
    
    For rw = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        If LCase(Right(Cells(rw, "C"), Len(EndsWith))) <> LCase(EndsWith) And _
            IsNumeric(Replace(Replace(Left(Cells(rw, "C"), 4), " ", "X"), "-", "X")) = False Then
            If DeleteRange Is Nothing Then
                Set DeleteRange = Cells(rw, "A").EntireRow
            Else
                Set DeleteRange = Union(DeleteRange, Cells(rw, "A").EntireRow)
            End If
        End If
    Next
    
    If Not DeleteRange Is Nothing Then
        DeleteRange.Delete
    End If
    
End Sub

Open in new window

0
 
NVITAuthor Commented:
Hi Wayne,

It's not deleting the "001 Billing Group / total Total Fee" lines. Is it a fairly quick fix?
It's deleting the rest, though. Thanks for that.
0
 
Wayne Taylor (webtubbs)Commented:
According to the rules you posted, it shouldn't delete those lines...

- does not end with string: / total Total Fee
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I assume you need to replace And with Or in line#7 in the end.
0
 
Wayne Taylor (webtubbs)Commented:
I assume you need to replace And with Or in line#7 in the end.

Could be. I read the rules as being 2 separate conditions, not the one.
0
 
NVITAuthor Commented:
@Wayne

I meant it as:
- does not end with string: / total Total Fee
- [b]and[/b] does not start with at least a minimum length 4 character number string. These pass: 2100, 9812

Please forgive me for not being clear.

@Subohd

That's it. It works.

Thank you both. This is very helpful.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad we could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now