-
Using Collection Object
Dear All,
I am trying to delete rows that contain any one of the following names e.g. "United Kingdom".
My VBA code is as follows:
Code:
Workbooks("VAR_test.xls").Activate
Dim badnames As New Collection
badnames.Add key1, "Germany"
badnames.Add key1, "United Kingdom"
badnames.Add key1, "United Stated of America"
badnames.Add key1, "United States of America"
badnames.Add key1, "Japan"
Dim CreditSheet As Worksheet
Set CreditSheet = Worksheets("Risk Report Output")
Dim Folder As Range
Set Folder = CreditSheet.Range("S6:S1000")
Dim c As Range
Dim totalNumRows As Integer
totalNumRows = 1000
For r = 5 To totalNumRows
Set c = CreditSheet.Cells(r, 19)
On Error GoTo notbad
If badnames.Item(c.Value) = key1 Then
CreditSheet.Rows(c.Row).Delete
r = r - 1
totalNumRows = totalNumRows - 1
End If
GoTo donethat
notbad:
Resume donethat
donethat:
Next
End Sub
I have no idea why this is refusing to delete the appropriate lines... using the debugger the "key1" tag is showing to be empty even though I have assigned them ?
Your help will be appreciated.
regards
mp16