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.
I get Compile Error: Object Required
I'm trying to model the code after:
Code:
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:
Code:
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