CODEWITHSUNDEEP
excelvba
Cheeky
Cheeky

Reputation: 13

Find and delete row with multiple criteria

I have a list of student records listed with surname in column B and first name in column C. On the user form is a search function which fills in the student name into the value surname.value and firstname.value, i then want a command button to find that person on the record list and delete the entire row. I had this coding working with message boxes, i then changed the message box to delete row and now it does not work.

Private Sub CommandButton1_Click()

Dim ws As Worksheet
Set ws = Worksheets("-student records no CVI")


     Dim rngFound As Range
    Dim strFirst As String
    Dim strID As String
    Dim strDay As String

    strID = Surname.Value
    strDay = Firstname.Value

    Set rngFound = Columns("B").Find(strID, Cells(Rows.Count, "B"), xlValues, xlWhole)
    If Not rngFound Is Nothing Then
        strFirst = rngFound.Address
        Do
            If LCase(Cells(rngFound.row, "C").Text) = LCase(strDay) Then
                'Found a match
                ws.Rows(rngFound.row).EntireRow.Delete

            End If
            Set rngFound = Columns("B").Find(strID, rngFound, xlValues, xlWhole)
        Loop While rngFound.Address <> strFirst
    End If

    Set rngFound = Nothing

End Sub

Upvotes: 1

Views: 223

Answers (2)

FreeSoftwareServers
FreeSoftwareServers

Reputation: 2801

I am by no means a professional just FYI, I'm not sure if this is the best way to do this, but it's how I'd do it.

Update (I did say I wasn't a professional!):

The second answer code will first search for Surname, then check Given Name, but it would match "John Smith" to "John Smithfalls" as it doesn't do "exact string", just needs the string to be present. It is also less efficient then using "AutoFilter". Based on the other answer and comments I made another answer as well to update this. (Accepted answers always show on top of page regardless of votes).

Using AutoFilter:

Sub Remove_Student()

Dim wb As Workbook, ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")

Dim SurnameStr As String, GivenNameStr As String
SurnameStr = "Smith"
GivenNameStr = "Joe"

With ws
 .AutoFilterMode = False
 With .Range("A1")
  .AutoFilter field:=2, Criteria1:=SurnameStr
  .AutoFilter field:=3, Criteria1:=GivenNameStr
 End With
 .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
 .AutoFilterMode = False
End With
End Sub

Using InStr:

Sub Remove_Student()

Dim wb As Workbook, ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")

Dim SurnameStr As String, GivenNameStr As String
SurnameStr = "Smith"
GivenNameStr = "Joe"

Dim lRow
lRow = ws.Cells(Rows.Count, "B").End(xlUp).Row

Dim rcell As Range, rng As Range
Set rng = ws.Range("B1:B" & lRow)

For Each rcell In rng.Cells
 If InStr(1, Range(rcell.Address), SurnameStr, vbTextCompare) <> 0 Then
 'MsgBox SurnameStr & " Found in Row " & rcell.Row
  If InStr(1, Range(rcell.Offset(0, 1).Address), GivenNameStr, vbTextCompare) <> 0 Then
   'rcell.EntireRow.Delete
   MsgBox SurnameStr & " , " & GivenNameStr & " Found in Row " & rcell.Row
  End If
 End If
Next rcell

End Sub

Upvotes: 0

Dhirendra Kumar
Dhirendra Kumar

Reputation: 333

Simple way of deleting rerords using Autofilter:

Sub Remove_Student()
Dim Ws As Worksheet
Dim strID As String
Dim strDay As String

Set Ws = ThisWorkbook.Sheets("Sheet1")
strID = Surname.Value
strDay = Firstname.Value

Ws.AutoFilterMode = False
Ws.Range("A1").AutoFilter 2, strID
Ws.Range("A1").AutoFilter 3, strDay
Ws.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Ws.AutoFilterMode = False

End Sub

Upvotes: 3

Related Questions