How to check the value of the first letter of a string using the vba array

Hi there,

Below is some code from a macro I need to change. How do we change the code that is in bold and underlined to VBA.Array(all strings whose first letter does not begin with "D")?

Sub Delete_Code()

Dim rngFound As Range, rngToDelete As Range
Dim strFirstAddress As String
Dim varList As Variant
Dim lngCounter As Long

Application.ScreenUpdating = False

varList = VBA.Array("AMEX", "CASH", "CC", "CK", "COB", "CONAC", "CONDD", "CONDENT", "CONSG", "CONSG HMO", "DISC", "IBAL", "IDA", "IP", "IPDENT", "IPSG", "IWO", "IWODENT", "MC", "No code", "TIMEPAY", "VISA", "WOA", "WOB", "WOC", "WOD", "WOE", "WOF")

For lngCounter = LBound(varList) To UBound(varList)

With Worksheets("DO2").Range("C:D")
Set rngFound = .Find( _
What:=varList(lngCounter), _
Lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True _
)


If Not rngFound Is Nothing Then
If rngToDelete Is Nothing Then
Set rngToDelete = rngFound
Else
Set rngToDelete = Application.Union(rngToDelete, rngFound)
End If

strFirstAddress = rngFound.Address
Set rngFound = .FindNext(After:=rngFound)

Do Until rngFound.Address = strFirstAddress
Set rngToDelete = Application.Union(rngToDelete, rngFound)
Set rngFound = .FindNext(After:=rngFound)
Loop
End If
End With
Next lngCounter

If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete

Application.ScreenUpdating = True

End Sub

Thanks so much

Instead of changing the array, why not just add a condition as you loop through it?

Please Login or Register  to view this content.

Hi Keyston,

I'm getting an error message "End If without block If"

How do I go about resolving that?

Thanks