Find and Delete sub

[arjking]

I'm writing a sub that compares two tables on the same sheet. It loops through the first table, checks if a cell in a certain column is blank. If so, it should go to the second table and find and delete each row in the second table that shares a field value "strState" with the observation in the first table.

Private Sub RemoveEntries()
  Dim lngLoop As Long 'loop variable
  Dim lngDeleteRow As Long 'the row about to be deleted
  Dim strState As String 'the string that I get from table 1 and I need to find in table 2
 
  With Workbooks(wbFutureName).Sheets(wbImportName)
  For lngLoop = 3 To LastRow
    If .Range("I" & lngLoop).Value = "" Then
      strState = .Range("B" & lngLoop).Value
      Do
        Set lngDeleteRow = .Range("DB3:DB" & LastRow).Find(What:=strState, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
        .Range("DA" & lDeleteRow & ":DP" & lDeleteRow).Delete (xlShiftUp)
 
      Loop While Not .Range("DB3:DB" & LastRow).Find(What:=strState, After:=.Range("DB3"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious) Is Nothing
    End If
  Next lngLoop
  End With
End Sub

I get Compile Error: Object Required


I'm trying to model the code after:

Sub FindLastCell()
Workbooks(wbFutureName).Sheets(wbImportName).Activate
If WorksheetFunction.CountA(Cells) > 0 Then
  'Search for any entry, by searching backwards by Rows.
  LastRow = Cells.Find(What:="*", After:=[A1], _
  SearchOrder:=xlByRows, _
  SearchDirection:=xlPrevious).row
  'Search for any entry, by searching backwards by Columns.
  LastColumn = Cells.Find(What:="*", After:=[A1], _
  SearchOrder:=xlByColumns, _
  SearchDirection:=xlPrevious).Column
  'MsgBox Cells(LastRow, LastColumn).Address
End If
End Sub

 

[John_w]

Deleting the 'Set' in

Set lngDeleteRow = .Range("DB3:DB" & LastRow).Find(What:=strState, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

will fix the compile error.

 

[arjking]

Thank you for the quick reply.

If I take away the "Set" command then I get Run-time error 91, Object Variable or With Block variable not set....

 

[John_w]

Replace your Do Loop with:

      Do
        Dim rFind As Range
        Set rFind = .Range("DB3:DB" & lastrow).Find(What:=strState, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        If Not rFind Is Nothing Then
            lngDeleteRow = rFind.Row
            .Range("DA" & lngDeleteRow & ":DP" & lngDeleteRow).Delete (xlShiftUp)
        End If
      Loop Until rFind Is Nothing

Also, always put Option Explicit at the top of every module. Tools - Options - Require variable declaration will put it there for you automatically. This would catch the undeclared variable lDeleteRow.

 

[arjking]

That worked, thank you.