Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 112
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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