PDA

View Full Version : Delete Entire Row Based on a Selection



MikeeRDX
12-08-2016, 05:25 PM
I have a worksheet (Sheet1) containing data and have created a userform to retrieve one of the data based on a user selection. Once the record has been retrieved, I would like to add a delete button my userform to delete any rows that contain that record. The loop doesn't have to look at multiple criteria but just a single one, say the value that is in txtName.

The logic that I have is upon the user clicking on "Delete", the value in txtName will be save at cell C1 on sheet2. The VBA code will loop and look for any record on column A in Sheet1 that matches the value in C1 on Sheet2 and delete entire row. There may be multiple records with matching value along column A so the loop will look and delete all matching records.

Can someone assist me with the code to accomplish this? Thank you!

p45cal
12-08-2016, 05:54 PM
Supply a worksheet to play with please.

MikeeRDX
12-09-2016, 08:50 AM
Please see attached for a sample file. Thank you for your help!

MikeeRDX
12-09-2016, 08:54 AM
Supply a worksheet to play with please.

Hi p45Cal, I have attached a sample file for you to play with. In the sample file, the one column of reference I would like to use is the Associate Name. So, once the user has selected a particular associate from the ComboBox, review and confirm the latest information that is displayed on the Userform, click on the Delete button and I would like to have a loop to go through the entire database in the Data sheet and delete all rows for any record matching that name.

Ideally, I would prefer to delete a range of columns (say columns A-F) if there is a match but if this is not possible, then deleting an entire row will be fine.

p45cal
12-09-2016, 01:41 PM
Add this to your userfrom's code-module:
Private Sub cmdDelete_Click()
Dim RngToDelete As Range
With ThisWorkbook.Sheets("Data")
For Each cll In .Range(.Range("C2"), .Cells(.Rows.Count, "C").End(xlUp)).Cells
If cll.Value = cboAssociates Then
If RngToDelete Is Nothing Then Set RngToDelete = cll Else Set RngToDelete = Union(RngToDelete, cll)
End If
Next cll
End With
'If Not RngToDelete Is Nothing Then RngToDelete.EntireRow.Delete 'the entire row gets deleted.
If Not RngToDelete Is Nothing Then RngToDelete.Offset(, -2).Resize(, 6).Delete xlShiftUp 'only columns A:F get deleted.
End Sub
but be aware that your 'the latest information that is displayed on the Userform' is not necessarily what your code shows; it shows the first record it comes across as it goes down the list.

MikeeRDX
12-09-2016, 03:34 PM
You are "The Man", p45Cal. It worked like a charm. There is a preceding process that I built to look of the record with the latest date to display on the form.

THANK YOU AGAIN FOR ALL THE HELP you've given me. :clap::clap::clap::clap: