VBA VLookUp issue

[wist]

Hey guys,

I have a little issue here, although I've found similar topics discussing this issue I am apparently not too bright to implement it into my script.

Basically when macro finds "AB" value in the sheet it deletes the whole row with that value. So far, so good.

Sub Find()
b = 2
c = Application.CountA(range("A:A")) - 1


On Error Resume Next
Do Until b > c
If Application.WorksheetFunction.VLookup("AB", Cells(b, Application.WorksheetFunction.Match("Type", range("A1:L1"), False)), 1, False) = "AB" Then
Range(Cells(b, 1), Cells(b, 12)).Delete shift:=xlUp
End If
b = b + 1
Loop
End Sub

The problem here is that when cells are shifted up there may be another "AB" line which got shifted but macro is already looking on the line that is below so it stays there

I guess what I need is to do 'b = b + 1' only if there is no "AB" value in the line

I tried the below code which is good only if "AB's" are lined up from the start and if I enable On Error Resume Next it will just delete the whole thing

Sub Find()
b = 2
c = Application.CountA(range("A:A")) - 1


[COLOR=#0000ff]'On Error Resume Next[/COLOR]
Do Until b > c
If Application.WorksheetFunction.VLookup("AB", Cells(b, Application.WorksheetFunction.Match("Type", range("A1:L1"), False)), 1, False) = "AB" Then
Range(Cells(b, 1), Cells(b, 12)).Delete shift:=xlUp
[COLOR=#0000ff]Else[/COLOR]
[COLOR=#0000ff]b = b + 1[/COLOR]
[COLOR=#0000ff]End If[/COLOR]
Loop
End Sub

I hope I made it clear enough

Any ideas please?

Thanks

 

[bobsan42]

have you tried it backwards - b changing from c down to 2 ?

 

[wist]

have you tried it backwards - b changing from c down to 2 ?

interesting suggestion, will give it a try and get back in a bit

 

[wist]

have you tried it backwards - b changing from c down to 2 ?

awesome, I think I made it work now, god took me all day ..thank you very much for your easy fix bobsan42

here's the working code

Sub Find()
b = 2
c = Application.CountA(Range("A:A"))




On Error Resume Next
Do Until c < b
If Not Application.WorksheetFunction.VLookup("AB", Cells(c, Application.WorksheetFunction.Match("Type", Range("A1:L1"), False)), 1, False) = "AB" Then
c = c - 1
Else
Range(Cells(c, 1), Cells(c, 12)).Delete shift:=xlUp
End If




Loop
End Sub