using VBA Delete rows using a 'table'

I need to be able to delete rows in Excel based on whether or not the first two characters in the table are in the cell in the spreadsheet.

i.e. I have a tab called 'Table'
in Colum 'A' I have

AT
CS
CZ
GR
HU
IL
PL
PO

[This can be added to so I need to look at the whole of column A]

I need my Macro to look up 'Table' and if in Sheet1 Column J any of the data starts with any of those listed in the Table, column 'A'  I need to delete the row.


So, if in Sheet1 in Column J 4 I have CZB123456789 I need to delete that row

if in Sheet1 in Column J 150 I have POV123556779 I need to delete that row

 
Thanks
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wshark83Commented:
here is the basic concept:

Sub Macro1()
    Range("B1").Select 'you can change column B1 to be the next blank column in your sheet1 data
    ActiveCell.FormulaR1C1 = "=VLOOKUP(LEFT(RC[-1],2),Table!A1,1,FALSE)" 'you will need to change RC[-1] to be the column you have defined - the number to column J
    Range("B1").Select ' same as the first line
    Selection.Copy
    Range("A1").Select
    Selection.End(xlDown).Select ' goes to the last row
    Range("B6").Select ' assumes there are 6 rows of data this can be changes to be more dynamic if required
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste 'copies and pastes the formula
    Application.CutCopyMode = False
    Selection.AutoFilter 'puts the filter on
    Selection.AutoFilter Field:=2, Criteria1:="<>#N/A", Operator:=xlAnd
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp ' delete all the rows which match
End Sub
0
Martin LissOlder than dirtCommented:
I've attached a workbook that contains this code.

Option Explicit

Public Function isInNamedRange(strNamedRange, strValue) As Boolean
    
    Dim r As Range
    
    With Range(strNamedRange)
        Set r = .Find(What:=strValue, _
                After:=.Cells(.Cells.Count), _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)
        If Not r Is Nothing Then
            isInNamedRange = True
        End If
    End With
    
End Function

Sub DeleteRows()
Dim lngRow As Long

Application.ScreenUpdating = False

For lngRow = Sheets("Sheet1").UsedRange.Rows.Count To 1 Step -1
    If isInNamedRange("MyNamedRange", Left(Cells(lngRow, 10).Value, 2)) Then
        Cells(lngRow, 10).EntireRow.Delete
    End If
Next

Application.ScreenUpdating = True
End Sub

Open in new window

I created a named range for the data on the Table sheet that's called 'MyNamedRange' which you can rename by going into the Name Manager.

To run the code you just need to run the DeleteRows macro.
Q-28272339.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
Thanks
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

JagwarmanAuthor Commented:
Martin I made a FOPAR. These are the ones I need to keep NOT delete. Can you help or shall I re-post?

Thanks
0
JagwarmanAuthor Commented:
Sorry having a bad day, I need to keep anything in the and delete anything that is not in the table.
0
Martin LissOlder than dirtCommented:
Just change line 28 to

If Not isInNamedRange("MyNamedRange", Left(Cells(lngRow, 10).Value, 2)) Then
0
JagwarmanAuthor Commented:
Thanks Martin, that's brilliant
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
JagwarmanAuthor Commented:
Martin,

would you be able to make your code keep row one which is the header. I tried by adding the first two letters of the header name from J1 into mynamedrange but it still deleted the first row

Thanks in advance
0
JagwarmanAuthor Commented:
Martin

Ignore my last request. Having a senior moment.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.