Excel userform deleting entire row not just intended selction

Question

I have a userform that has a drop down box in which a person can select a record to have deleted off a list.

The code below is deleting the ENTIRE ROW. I do not want that. I just want the cells between A:E cleared on my spreadsheet.

I am not sure how else to describe this so I apologize in advance. Here is the code:

Private Sub CheckBox1_Click()

End Sub

Private Sub CommandButton1_Click()

Dim lRw As Long
ActiveWorkbook.Sheets("RAWDATA").Visible = xlSheetVisible
 'get the row number. add 2 because ListIndex starts at one
lRw = Me.ComboBox1.ListIndex + 2

ActiveWorkbook.Sheets("RAWDATA").Select
Cells(lRw, 1).EntireRow.ClearContents
ActiveWorkbook.Sheets("RAWDATA").Visible = xlSheetHidden

End Sub



Private Sub CommandButton2_Click()
ComboBox1.Value = ""
ComboBox1.Clear
ComboBox1.Clear
Unload Me


End Sub

Private Sub UserForm_Initialize()
 'assumes data starts in A1 and has a header row
Me.ComboBox1.List = ActiveWorkbook.Sheets("RAWDATA").Cells(1, 2).CurrentRegion.Offset(1, 2).Value

End Sub

enter image description here

Your issue is with the line `cells(lRw, 1).EntireRow.ClearContents`. Look into selecting a the range you want and then clear contents. Something like `Range("A"&:"E"&).clear` should work. — Michael Harvey, Dec 05 '14 at 18:54
I am trying to clear the range that is automatically found by the user selecting the project they want to delete from the list. — Yogwhatup, Dec 05 '14 at 20:50
I just want to delete the data that is attached to what the user selects. So if they selected first twigg and hit the delete button it would clear A2 through J2. I want to retain the information to the right. — Yogwhatup, Dec 05 '14 at 20:54
The `cells(lRw, 1).EntireRow.ClearContents` is your issue. The `EntireRow` function is selecting the row which `cells(lRw, 1)` points to. The `ClearContents` function clears the row selected. You need to replace it with something like `Range("A" & & ":J" & < the row number>).clearcontents`. Your variable `lRw` is supposed to be the row in which the selected project is located, correct? If so, then `Range("A" & lRw & ":J" & lRw ).clearcontents` should work. — Michael Harvey, Dec 05 '14 at 21:25
Another side note. If you don't have anything inside your CheckBox1_click() you can delete it. To get that back, simply double click on the control in the Object Designer mode for the form. Although it doesn't look like you have a check box in that form. I suggest trying to look at each line and figure out what it's doing. For a while you can add a comment to each line just to really get a hold of what the code does. — peege, Dec 06 '14 at 04:13
Thank you ALL this was like a X-Mas miricle for me. I had been hitting my head on the table for hours trying to figure this out! THANK YOU THANK YOU THANK YOU!!!!! — Yogwhatup, Dec 07 '14 at 21:00

score 2 · Answer 1 · edited May 23 '17 at 11:52

side note: You don't need to select the cells to manipulate the contents in vba. Check out this link to explain that concept in more detail: how-to-avoid-using-select-in-excel-vba-macros

This is the problem code. You are clearing the entire row, using ".EntireRow.ClearContents"

ActiveWorkbook.Sheets("RAWDATA").Select
Cells(lRw, 1).EntireRow.ClearContents
ActiveWorkbook.Sheets("RAWDATA").Visible = xlSheetHidden

Here are three solutions. Both should give you some insight into how the .Cells(row,col) idea works while using a loop. You are using a variable to control the row number, and the same concept can be applied to the column. Even though it's just 5 columns. It might be 50 for another project. So you can loop through them using a "For Loop" This is my preferred method.

If you want to get loopy, try something like this. Use a Variable for the Column

For lCol = 1 To 5
    Sheets("RAWDATA").Cells(lRw, lCol).ClearContents
Next lCol

You can do one cell at a time Directly coding the column number:

Sheets("RAWDATA").Cells(lRw, 1).ClearContents
Sheets("RAWDATA").Cells(lRw, 2).ClearContents
Sheets("RAWDATA").Cells(lRw, 3).ClearContents
Sheets("RAWDATA").Cells(lRw, 4).ClearContents
Sheets("RAWDATA").Cells(lRw, 5).ClearContents

You can do one cell at a time Directly coding the column LETTER:

Sheets("RAWDATA").Cells(lRw, "A").ClearContents
Sheets("RAWDATA").Cells(lRw, "B").ClearContents
Sheets("RAWDATA").Cells(lRw, "C").ClearContents
Sheets("RAWDATA").Cells(lRw, "D").ClearContents
Sheets("RAWDATA").Cells(lRw, "E").ClearContents

edit: added some explanation and link

Thank you ALL this was like a X-Mas miricle for me. I had been hitting my head on the table for hours trying to figure this out! THANK YOU THANK YOU THANK YOU!!!!! — Yogwhatup, Dec 07 '14 at 21:00

Michael Harvey · Accepted Answer · 2014-12-06T06:13:22.583

The cells(lRw, 1).EntireRow.ClearContents is your issue. The EntireRow function selects the row which is pointed to by cells(lRw, 1). The .ClearContents function clears what's selected. You should replace it with something like:

Range("A" & <the row number> & ":J" & <the row number>).clearcontents

Your variable lRw is supposed to hold the value of the row in which the selected project is located, correct? If so, then:

Range("A" & lRw & ":J" & lRw ).clearcontents

should work. You can change the column letters to whatever you'd like to clear.

I think PJ Rosenburg's solutions are bit impractical, but I agree with the fact that you should shy away from using the .select function. You can do everything you need to do without using it. You'll write much better code once you understand this concept. In fact, here's a rewrite of your commandButton1_click that should do the exact same thing, but with less code and is easier to read.

Private Sub CommandButton1_Click()

    Dim lRw As Long
    lRw = Me.ComboBox1.ListIndex + 2

    with ActiveWorkbook.Sheets("RAWDATA")

        .Visible = xlSheetVisible
        .Range("A" & lRw & ":J" & lRw ).clearcontents
        .Visible = xlSheetHidden

    end with

end sub

Notice a couple of things:

No .select
Moving the assignment statement
The addition of the With/End With statements

Anyway, I hope this helps and better explains what I was trying to say earlier.

Thank you ALL this was like a X-Mas miricle for me. I had been hitting my head on the table for hours trying to figure this out! THANK YOU THANK YOU THANK YOU!!!!! Sorry...just noticed that you are not supposed to think someone here, but I am just so happy :) — Yogwhatup, Dec 07 '14 at 21:01
My solution was not meant to be the most practical, but focusing on informative, with some options. Also, at the time you had only stated your solution in the form of a comment, so someone had to type an answer up. — peege, Dec 07 '14 at 21:04
My apologies PJ. My comment was not meant to be a jab at you or your code. I sincerely apologize if you took it that way. — Michael Harvey, Dec 07 '14 at 21:41

Excel userform deleting entire row not just intended selction

2 Answers2