troubleshooting Question

How to delete cells that begin with using VBA?

Avatar of Legolas786
Legolas786 asked on
Visual Basic Classic
6 Comments1 Solution122 ViewsLast Modified:
Hi,

Is it possible in VBA to delete all the cells that begin with a certain letter/word/number? I use the following but it works more like if the whole cell contains the values, another problem I have is that if i have a cell that begins with a date for example "12/12/2014 text" the below code would not work.

Set rngH = wst.Range("H1:H" & wst.Range("H65536").End(xlUp).Row)

For Each rng In rngH
    bnlFoundInvalid = False
    bnlFoundValid = False
   For intCounter = 0 To 12
        If InStr(1, rng.Offset(0, intCounter).Value, "Set", vbTextCompare) > 0 Or _
            InStr(1, rng.Offset(0, intCounter).Value, "New", vbTextCompare) > 0 Or _
            InStr(1, rng.Offset(0, intCounter).Value, "1", vbTextCompare) > 0 Or _
            IsNumeric(rng.Offset(0, intCounter).Value) Then
            If bnlFoundInvalid = False Then
                'An invalid column.
                intColInValid = intCounter
            End If
            bnlFoundInvalid = True
            rng.Offset(0, intCounter).ClearContents
        Else
            '1st valid column.
            If bnlFoundValid = False Then
                intColValid = intCounter
            End If
            bnlFoundValid = True
        End If
        If bnlFoundInvalid = True Then
            If bnlFoundValid = True Then
                If intColValid > 0 Then
                    'Col H had an invalid value.
                    rng.Value = rng.Offset(0, intColValid).Value
                End If
            End If
        End If
    Next intCounter
Next rng
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros