VBA VLookUp issue

wist

Board Regular
Joined
Nov 8, 2012
Messages
79
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.

Code:
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 :mad:

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
Code:
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 :confused:

Any ideas please?

Thanks
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
have you tried it backwards - b changing from c down to 2 ?
 

wist

Board Regular
Joined
Nov 8, 2012
Messages
79
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
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,542
Messages
5,529,453
Members
409,878
Latest member
DDhol
Top