Solved: Excel 2003 Like Operator

Discussion in 'Business Applications' started by computerman29642, Apr 4, 2008.

Not open for further replies.

Joined:
Dec 4, 2007
Messages:
2,895
Does anyone know how to use the LIKE operator in an Excel macro? As far as I know, wildcards cannot be used in an Excel macro, so I thought the next best thing would be the LIKE operator.

What I am trying to do is take the following code....

Code:
```For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(r, 1) = "" Then Rows(r).Delete
Next r
```
Instead of it deleting rows that are blank, I was hoping change the following line:

Code:
```If Cells(r, 1) = "" Then Rows(r).Delete
```
TO

Code:
```If Cells(r, 1) = LIKE(ac) OR Like(Sample)  Then
'Do Nothing
Else
Rows(r).Delete
```
If anyone has any suggestions, they will be greatly appreciated.

2. Ziggy1

Joined:
Jun 17, 2002
Messages:
2,551
take the equals out before LIKE, and use quotes around the text to compare. You need the wildards for it to work

Public Sub test()

Dim x As Integer

For x = 1 To 10

If Range("A" & x).Value Like ("AC*") Or Range("A" & x).Value Like ("SAMPLE") Then

Else

Rows(x).Delete

End If

Next

End Sub

Joined:
Dec 4, 2007
Messages:
2,895
Ziggy,

Thank you for the reply. I will give a shot, and let you know how it works.

4. Ziggy1

Joined:
Jun 17, 2002
Messages:
2,551
actually I should have put it the way you had...

Code:
```Public Sub test2()

For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1

If Cells(r, 1) Like ("AC*") Or Cells(r, 1) Like ("SAMPLE") Then

Else

Rows(r).Delete

End If

Next r

End Sub```

Joined:
Dec 4, 2007
Messages:
2,895
This works great. Thanks very much Ziggy.

Joined:
Jun 17, 2002
Messages:
2,551
np

7. Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
hi Chris. Is this like the issue we were discussing on your workbook the other day and the formulas which evaluate to null? if so, we'll be able to work with this with the dynamic range of formulas and not need an expensive loop. Try to stay away from loops like we were talking about. I'll await your response for further.

Sent via mobile device.

Joined:
Dec 4, 2007
Messages:
2,895
Zack,

Yes & No. The code I was asking about here does not really tie in with the workbook, but I needed it to setup a file that will be copied into the workbook. The machine that created the file places a lot of extra lines and headers that are not needed.

As Seen On

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!