Need to delete rows that match values selected in combobox

[patojachula]

This is my first post. I know next to nothing about VBA, other than what I have learned by trying and testing different bits of code. Currently I am creating a excel file that will track various tasks assigned to people in the office. I have created userforms to add and update tasks. I'm having trouble with the update form. I can pull in the information I need and update and change it, but what I want to do is to have a delete button that lets a user delete a task. Selection of the task is made by two comboboxes, the first is the client name and the second is the task. Depending on the selection, I need to then search a worksheet and find and delete the row that has both of the values the user selected. I can code it to delete based on one value (something I did in the spreadsheet that tracks the clients), but not two. I can imagine a scenario where the same task is assigned to different clients and using two values I think I can ensure that I'm deleting the right row. The code I have works sporadically, sometimes it will delete the row, but most of the time it won't. What am I doing wrong?

This is the code I used for the two comoboboxes:

Private Sub cmdDelete_Click()

Dim colARowCount As Integer
Dim colBRowCount As Integer
Dim totalRows As Integer
Dim currentRow As Integer
Dim colA As Integer
Dim colB As Integer
Dim colK As Integer
Dim colL As Integer
Dim colACurrentValue As String
Dim colBCurrentValue As String
Dim colKCurrentValue As String
Dim colLCurrentValue As String
Dim WS As Worksheet

Set WS = ThisWorkbook.Worksheets("Current")

    colA = 1
    colB = 2
    colK = 11
    colL = 12
    
    WS.Cells(1, colK) = Me.cmbClient.Text
    WS.Cells(1, colL) = Me.cmbTask.Text
    
    colKCurrentValue = WS.Cells(1, colK)
    colLCurrentValue = WS.Cells(1, colL)
         
    colARowCount = WS.Cells(Rows.Count, colA).End(xlUp).Row
    colBRowCount = WS.Cells(Rows.Count, colB).End(xlUp).Row

    totalRows = colARowCount
    If colARowCount < colBRowCount Then
        totalRows = colBRowCount
    End If

ans = MsgBox("Delete this task?", vbYesNo)
        If ans = vbNo Then
            Exit Sub
        
        ElseIf ans = vbYes Then

    For currentRow = totalRows To 2 Step -1
        colACurrentValue = Cells(currentRow, colA).Text
        colBCurrentValue = Cells(currentRow, colB).Text

               If colACurrentValue = colKCurrentValue _
                    And colBCurrentValue = colLCurrentValue Then
                        Cells(currentRow, colB).EntireRow.Delete
                End If
                
    Next currentRow
    
End If
    
MsgBox "This task has been deleted."
Unload FormUpdateTask

End Sub

In case you're curious this is how I did it with one combobox:

Private Sub cmdDeleteCase_Click()
Dim ws As Worksheet
Dim x As Long
Dim fCell As Range
Set ws = ThisWorkbook.Sheets("Current")
cMatter = cbMatter.Value
        
    With ws
        Set fCell = .Range("B:B").Find(cbMatter.Value, , xlValues, xlWhole)
        
        If fCell Is Nothing Then Exit Sub
               
            x = fCell.Row
            ans = MsgBox("Delete " & cMatter & " from list?", vbYesNo)
            If ans = vbYes Then
                .Range("B:B").Cells(x, 1).EntireRow.Delete Shift:=xlUp
                    ElseIf ans = vbNo Then
                        Exit Sub
                               
        End If
    
    End With
        
MsgBox (cMatter & " has been deleted.")
Unload FormDeleteCase
              
End Sub

[mehmetcik]

This is getting close to how I would approach this.

It looks like you are searching column B for one of the search strings, it isn't obvious if that is K1 or L1

Having found a match,

You should Select that row and use find or match to find the second search String.

As you did not post your workbook I modified your code and tested it using a spreadsheet with text in K1 and L1

I pasted k1 into several places in column B and L1 in an adjacent cell.

Sub Test()
Dim ws As Worksheet
Dim x As Long
Dim fCell As Range
Dim fCell2 As Range
Set ws = ThisWorkbook.Sheets("Current")
        
'This is the first row in column B to search from.
Pos = 1

'This is the last occupied row in column B.
LR = Cells(Rows.Count, 2).End(xlUp).Row

LoopStart:
    With ws

'Searching column B.
        Set fCell = .Range(Cells(Pos, 2), Cells(LR, 2)).Find(Range("K1").Value, , xlValues, xlWhole)
        
        If fCell Is Nothing Then Exit Sub
               
        x = fCell.Row
  
'Searching within that row
  Set fCell2 = .Rows(x & ":" & x).Find(Range("L1").Value, , xlValues, xlWhole)
 
'If no match in row the loop back
  If fCell2 Is Nothing Then Pos = x + 1: GoTo LoopStart
            
            ans = MsgBox("Delete " & Range("K1").Value & " from list?", vbYesNo)
            If ans = vbYes Then
                .Range("B:B").Cells(x, 1).EntireRow.Delete Shift:=xlUp
                    ElseIf ans = vbNo Then
                        Exit Sub
                               
        End If
    
    End With
        
MsgBox (Range("K1").Value & " has been deleted.")
              
End Sub

[patojachula]

Thank you so much! I totally didn't expect you to do the code for me. I'm sorry you had to write and test the code, but I didn't know how to load my Excel file. This is the link to the file in Google Drive https://drive.google.com/file/d/0B2O...ew?usp=sharing. The code probably looks a little Frankenstein-esque because I revised code from various sources to do what I need it to do. The add button is dependent on another workbook, but everything else should be fine. I fixed the runtime error I was getting with the code you provided and it works great on rows 1-7, but if I try to run it on row 8 and down, it just locks up. You don't have to code it for me, but could you please point me in the right direction as to what I need to do to keep it from locking up? Thanks so much!

[mehmetcik]

There is no data, and no instructions.

[patojachula]

I'm so sorry. Thanks for being patient with me. So basically there are two workbooks - Master Case List and Task Master. The Master Case List is basically a database of current cases and Task Master will be used to assign and track various client related assignments. The workbook I'm attaching (via hyperlink below) is the Task Master. Because not everyone in the office is tech savvy, to prevent unwanted changes to the actual data, the Current, Completed, and Datalist sheets contain the data and are hidden and the TaskMaster sheet contains various buttons that are assigned macors that allow a user to add to or modify the data on the other sheets or copy the relevant data to the TaskMaster sheet to allow a user to filter, sort, and print various task lists to their hearts' desire without endangering the underlying data. The Add button triggers a user form FormAddNewTask to add a new task. FormAddNewTask has a combobox that is populated using the current clients from the Master Case List, the data that is input on the FormAddNewTask populates the Current sheet in Task Master. For testing purposes, I have just added some generic data to the Current sheet. The Update button allows a user to modify an existing task. When FormUpdateTask is triggered it populates the two comboboxes based on the data in the Current sheet. After selecting the client and the task, the remaining fields are populated once the user hits enter. (I think I've figured out how to automate that, but I'll work on that code later.) What I am having problems with is the Delete button on the user form FormUpdateTask that is triggered by clicking the Update button. Once you click on the delete button, it searches for the values selected in the comboboxes and deletes the row that contains both values. The code you provided works like a charm on rows 2-7, but on rows 8 and down, it just freezes and it is expected that this list will grow quite large. Again, you've done so much if you could please just point me in the right direction to keep it from locking up, it would be much appreciated. I tried various searches, but couldn't find anything that helped. Thanks again!

You can access the workbook Task Master here: https://drive.google.com/file/d/0B2O...ew?usp=sharing.

[patojachula]

I was able to modify the code I found here (http://www.rondebruin.nl/win/winfile...DeleteCode.txt) to get it to work:

Private Sub DeleteCommand()
    Dim DeleteValue1 As String
    Dim DeleteValue2 As String
    Dim rng As Range
    Dim calcmode As Long

    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    DeleteValue1 = Me.cmbClient.Value
    DeleteValue2 = Me.cmbTask.Value

Sheets("Current").AutoFilterMode = False
Sheets("Current").UsedRange.AutoFilter
Sheets("Current").UsedRange.AutoFilter field:=1, Criteria1:=DeleteValue1
Sheets("Current").UsedRange.AutoFilter field:=2, Criteria1:=ClientTask

With Sheets("Current").AutoFilter.Range
    On Error Resume Next
    Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
              .SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If Not rng Is Nothing Then rng.EntireRow.Delete
End With

Sheets("Current").AutoFilterMode = False
   
    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With

End Sub

Thanks for your input!