vba Delete Row and Member

[RasGhul]

Hi Team,

I have a 200 member data set columns A:I and a combo box with the members names DV. When I select someones name from the combo box I would like to delete that row from A:I. I was tinkering with the selection.delete shift:= xlUp function but couldn't define the needed objects etc. it works when I record a macro but I would like to be able to delete who I select from the Combo box.


Thanks in advance all.

 

[JackDanIce]

Record the macro and post the code, it's a good way to understand what you want your sheet to do, without benefit of seeing your PC screen.

Is there only ever 1 unique name or duplicates? If duplicates, have you considered filtering your data for that name and then deleting those rows? Again, record the macro to do this and post that code.

Also, state the name of the sheet, as usually this isn't clear from the recorded macro if you're not changing sheets.

 

[RasGhul]

Thanks Jack for the reply

So I've mapped the row addresses and as the Combobox1 value changes to different members names the L2 value is the address that needs to be deleted like in the 'recorded macro("A9:F9"). The vba needs to read the value from L2 and select that for delete.

Sorry I realize it would be easier just to right click and delete the row, but I'm trying to make this sheet easier for the owner...

Private Sub ComboBox1_Change()


Sheets("Members").Range("H2").Value = Me.ComboBox1.Value


End Sub


Private Sub CommandButton1_Click()


' delmember2 Macro
'
Dim r As Range
Dim sta As String


Set r = Sheets("MEMBERS").Range("L2").Value


    sta = r.Value.Select
    
'recorded macro
    Sheets("MEMBERS").Range("A9:F9").Select
    Selection.Delete Shift:=xlUp
    Sheets("MEMBERS").Range("A6").Select
    
    
    Sheets("MEMBERS").Range("H2").ClearContents
    
    ThisWorkbook.RefreshAll
    


End Sub


Private Sub CommandButton2_Click()


    Unload Me


End Sub


Private Sub UserForm_Initialize()


Sheets("MEMBERS").Range("H2").ClearContents




End Sub

 

[JackDanIce]

Try this for Private Sub CommandButton1_Click which will delete all rows in A:F where row in A matches L2 (starting at A3 to the last row in column A):

Private Sub CommandButton1_Click()

' delmember2 Macro
Dim x   As Long
Dim LR  As Long

Application.ScreenUpdating = False

With Sheets("MEMBERS")
    LR = .Cells(.Rows.count, 1).End(xlUp).row
    For x = 3 To LR
        If .Cells(x, 1).Value = .Cells(2, 12).Value Then .Cells(x, 1).ClearContents
    Next x
    
    On Error Resume Next
    .Cells(3, 1).Resize(LR - 2, 6).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
    On Error GoTo 0
    .Cells(2, 8).ClearContents
End With
    
ThisWorkbook.RefreshAll

Application.ScreenUpdating = True
    
End Sub

Untested, so save your work before running or make a copy of your workbook to test on.

 

[RasGhul]

Thanks Jack, but I only want to delete one row that matches the L2 value so we can remove that member. As in the screenie below as we select names with the combobox the row range to delete is in L2. When I run vba in this case it should delete the row only from A9:F9. (I know the pic as H9 but I've deleted some columns since.)

 

[JackDanIce]

Try:

Private Sub CommandButton1_Click()

' delmember2 Macro
Dim x   As Long
Dim LR  As Long


Application.ScreenUpdating = False


With Sheets("MEMBERS")
    LR = .Cells(.Rows.count, 1).End(xlUp).row
    
    On Error Resume Next
    .Cells(3, 1).Resize(LR - 2).find(what:=.Cells(2, 8).Value, LookIn:=xlValues, lookat:=xlWhole).Resize(, 6).Delete shift:=xlUp
    On Error GoTo 0
    
    .Cells(2, 8).ClearContents
End With
    
ThisWorkbook.RefreshAll


Application.ScreenUpdating = True
    
End Sub

 

[Ras GhuL]

Thanks JackDanIce works brilliantly.