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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,593
Messages
5,838,257
Members
430,536
Latest member
Manoj Gaidhankar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top