Find Method to Delete Row

[avd88]

Hi,

I'm trying to use the find method to look in another sheet for a value in a selected row that will be stored in a variable.
Once the macro finds that value it should delete the entire row.

Private Sub IDNRemoveFlagButton_Click()
    Dim y As Integer
    Dim varId As String
    
    y = ActiveCell.Row
    varId = ActiveSheet.Cells(y, 1).Value
    
    Dim mbResult As Integer
    mbResult = MsgBox("Unflag " & varId & "?", _
    vbYesNo)

    Select Case mbResult
        Case vbYes
    
        Case vbNo
        Exit Sub

    End Select


    'CLEAR FLAG'

    If ActiveSheet.Name = "IDN" Then

        Here is where I need to find varId on the sheet IDN_ column A:A and once it finds it delete that row.

    If ActiveSheet.Name = "BT" Then

        Same thing...

    End If
End Sub

Thanks for the help!

 

[johnnyL]

Your code is incomplete so it may be more difficult for me to see what all is going on.

Looks like your wanting to search column A in a sheet for a value that is saved into 'varId'. If that value is found in the column A, you want to delete that row from that sheet that has the value in column A? After you find the value, do you want to continue to search that column A for more matches, or will there be only one possible match to find?

You may also want to look here <--- for a very similar thread.

 

[avd88]

That's exactly what I'm trying to do.
There should only be 1 possible match.

 

[johnnyL]

The following code should do the find/delete for your sheet called 'IDN':

    Dim LastRowInRange  As Long, RowCounter As Long
'
    LastRowInRange = Sheets("IDN").Range("A:A").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row            ' Returns a Row Number
'
    For RowCounter = 1 To LastRowInRange                                ' Increment through cells in Column A
        If Sheets("IDN").Range("A" & RowCounter) = varId Then           '   If Cell matches our 'Delete if' value then
            Sheets("IDN").Rows(RowCounter).EntireRow.Delete             '       Delete the row
            Exit For
        End If
    Next

 

[johnnyL]

Sorry I forgot the 'Exit For', it is corrected now.