Delete rows where value in is list on seperate sheet

[Godders199]

Hello, I have the following code , which identifies rows to delete from a list on a seperate sheet. the only issue is i need to remember to change varList = Range("deselect!A1:A10").Value if the list has more than 10 entries.
I have tried adding a last row count into the VBA, but cannot get anything to work. SO effectively i am just looking for the code to replace varList = Range("deselect!A1:A10").Value with A1 and lastrow.

Sub remove()
Dim rngFound As Range, rngToDelete As Range
Dim strFirstAddress As String
Dim varList As Variant
Dim lngCounter As Long
Application.ScreenUpdating = False
Sheets("submissions").Select
varList = Range("deselect!A1:A10").Value


For lngCounter = LBound(varList) To UBound(varList)

With ActiveSheet.Range("d:d")
Set rngFound = .Find( _
What:=varList(lngCounter, 1), _
Lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True _
)

If Not rngFound Is Nothing Then
If rngToDelete Is Nothing Then
Set rngToDelete = rngFound
Else
Set rngToDelete = Application.Union(rngToDelete, rngFound)
End If

strFirstAddress = rngFound.Address
Set rngFound = .FindNext(After:=rngFound)

Do Until rngFound.Address = strFirstAddress
Set rngToDelete = Application.Union(rngToDelete, rngFound)
Set rngFound = .FindNext(After:=rngFound)
Loop
End If
End With
Next lngCounter

If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.delete
Application.ScreenUpdating = True

End Sub


Any help would be appreciated

 

[Fluff]

How about

    Dim UsdRws As Long
    UsdRws = Sheets("deselect").Range("A" & Rows.Count).End(xlUp).Row
    varList = Sheets("deselect").Range("A1:A" & UsdRws).Value

 

[Godders199]

thank you , works perfectly

 

[Fluff]

Glad to help & thanks for the feedback

 

[Rick Rothstein]

Hello, I have the following code , which identifies rows to delete from a list on a seperate sheet.

If I understand what your code is supposed to be doing, the following more compact macro should do the same thing (and probably more quickly)...

[table="width: 500"]
[tr]
	[td]Sub Remove()
  Dim X As Long, varList As Variant
  varList = Sheets("Submission").Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For X = 1 To UBound(varList)
    Range("D:D").Replace varList(X), "#N/A", xlWhole, , True, False, False
  Next
  On Error GoTo NothingOnListFound
  Columns("D").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
NothingOnListFound:
End Sub[/td]
[/tr]
[/table]