VBA and vlookup

[gunny2k9]

Morning all,

is it me being dumb or just not enough coffee yet...

Ok so being 8am this morning got asked to try clean up a report, thought ye no problem be easy, work that vba magic on it. :devil2:

So i wrote a quick test code


Sub proDelete()

Range("B1").Select

Do Until Selection.Value = ""

If Selection.Value = "UNEEDED" Then
'Selection.EntireRow.Delete
MsgBox "Delete ME"
Else
Selection.Offset(1, 0).Select
MsgBox "Keep ME"
End If
Loop

Range("A1").Select

End Sub


but this wasnt working.. to find out the the data in Column J is all in fact VLOOKUPs :doh:


=VLOOKUP(F2,'Data'!A:B,2,FALSE)


now i ve bever had to deal with VBA and VLOOKUPs together :dunno so, just wondering if thers an quick easy way to read the VLOOKUPS Value its returned like "UNEEDED" :help

THANKS IN ADVANCE

[gunny2k9]

ok nevermind lol think i have it working now .... LOL i think its me being dumb, 3 cups of coffe later ;P


Sub proDelete()

Range("J1").Select
Do Until Selection.Value = ""

If Selection.Value = "UNEEDED" Then
'Selection.EntireRow.Delete
MsgBox "Delete ME"
Selection.Offset(1, 0).Select
Else
Selection.Offset(1, 0).Select
'MsgBox "Keep ME"
End If
Loop

Range("A1").Select

End Sub


and can use something along the lines of (found on google, which i ve modded - not perfect)


Sub test()

result = [VLOOKUP(F2,'Route Data'!A:B,2,FALSE)]
Debug.Print result

If result = "UNEEDED" Then
Debug.Print "Delete"
Else
Debug.Print "Stays"
End If

End Sub


SIMPLES !! :rotlaugh:

but if anyone has any other ideas as i m in no way the best at vba i ll leave this thread open as handy to have this info for others

[Bob Phillips]

Not sure about the logic of that code at all.

I would do it like this


Sub proDelete()

Application.ScreenUpdating = False

lastrow = Cells(Rows.Count, "B").End(xlUp).Row

For i = lastrow To 2 Step -1

If Cells(i, "B").Value = "UNEEDED" Then

Rows(i).Delete
End If
Loop

Application.ScreenUpdating = True
End Sub

[gunny2k9]

i ll give that ago lol...

was just about to reply hmm think i need a better code as just run that and relized the report is 63977 rows :( OH DEAR !! :rofl:

PS this report isnt for me it for someone else, just trying to see if i can find vba code to help clean it as in reduce the data.

cheers

[gunny2k9]

Not sure about the logic of that code at all.

I would do it like this


Sub proDelete()

Application.ScreenUpdating = False

lastrow = Cells(Rows.Count, "B").End(xlUp).Row

For i = lastrow To 2 Step -1

If Cells(i, "B").Value = "UNEEDED" Then

Rows(i).Delete
End If
Loop

Application.ScreenUpdating = True
End Sub


get a Loop with out a Do error btw :P

[Bob Phillips]

Sorry, should be Next i not Loop towards the end.