Tweak my macro

[Shaz]

Hello
The macro below examines a column that has the heading "ID" in sheet
one and if it does not find a match within Column A in sheet "ID" it
deletes the row. I thought the "ID" column on Sheet 1 would always be
in Column C but alas this is not always the case.
It needs amending but being VBA challenged I'm struggling. Can you
help?

Thanks in advance.
Shaz

Public Sub Sorted()

Dim LRowData As Long, LRowTable As Long
LRowData = Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row
LRowTable = Sheets("ID").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Sheet1").Activate
Dim ir As Long, it As Long, NotFound As Boolean
For ir = LRowData To 1 Step -1
NotFound = True
For it = 1 To LRowTable
If Cells(ir, 3).Value = Sheets("ID").Cells(it, 1).Value Then
NotFound = False
Next it
If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp
Next ir
End Sub

 

[Dave Peterson]

I think that this'll work ok:

Option Explicit
Public Sub Sorted2()

Dim FoundCell As Range
Dim IDCol As Long
Dim curWks As Worksheet
Dim IDWks As Worksheet
Dim delRng As Range
Dim res As Variant
Dim myCell As Range
Dim myRng As Range

Set curWks = Worksheets("sheet1")
Set IDWks = Worksheets("ID")

With curWks
With .Rows(1) '.rows("1:2")???
Set FoundCell = .Cells.Find(what:="ID", _
after:=.Cells(.Cells.Count), LookIn:=xlValues, _
lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "ID Not Found!"
Exit Sub
End If

IDCol = FoundCell.Column

'start with row 2 to avoid the headers???
Set myRng = .Range(.Cells(2, IDCol), _
.Cells(.Rows.Count, IDCol).End(xlUp))
For Each myCell In myRng.Cells
res = Application.Match(myCell.Value, IDWks.Range("a:a"), 0)
If IsError(res) Then
'not found
If delRng Is Nothing Then
Set delRng = myCell
Else
Set delRng = Union(myCell, delRng)
End If
End If
Next myCell

If delRng Is Nothing Then
MsgBox "All matched"
Else
delRng.EntireRow.Select '.delete when you're done testing
End If
End With
End Sub


Watch out for your header row--where ID is kept. And Change that .select to
..delete once you verified that the correct stuff is being deleted.

 

[mudraker]

Shaz

one way is to add extra code to look through your headers till it finds
the ID column then set a integer varable to the column number and use
that variable instead of 3 as the column number

 

[Shaz]

Hi Guys
Thanks for responding. Hmm and I thought a tweak would have been
sufficient my apologies.
Dave the code works brilliantly just one thing as Mudraker points out
the ID row is never in the same place. Would an Input box be the
solution?
I don't know how to achieve this.

Regards
Shaz

 

[Shaz]

Hi Guys
Thanks for responding. Hmm and I thought a tweak would have been
sufficient my apologies.
Dave the code works brilliantly just one thing as Mudraker points out
the ID row is never in the same place. Would an Input box be the
solution?
I don't know how to achieve this.

Regards
Shaz

 

[Dave Peterson]

Do you really mean the ID row is never in the same place or the ID column.

If you meant the column, then I think we're done.

If you meant row, you could search for it, too. Then when you found it, use
that row+1 as the starting point for deleting data.

But your code looked like it started in Row 1 (for deleting).

So it kind of depends on what's supposted to happen.