Deleting cells from a specific row, specified by a combo box

[Smurlos]

Hi All

I've got a list of users within a sheet. The sheet is split vertically in two. So the users are in column L with password, nickname etc through to column P. They start from row 6. So the first user is in cell L6.

A combo box (selectuser) shows all the users from cell L6 to the last user on sheet "Setup". This works fine.

I've got my combo box linked to other text fields so when you select a user, it displays their nickname in the nickname field etc.

I have a delete button on my userform, which, when clicked, deletes the user.

If I use the following formula, it clears the cells from the row selected based on the selection in the combo box. However when you then load up the userform again, it loads all users fine on selecting from the combo box until you click on a user that was below the one you just removed. It also leaves those cells blank rather than just deleting them.

Please Login or Register  to view this content.

So I need to work out how to delete the cells and shift the ones below up a row, based on the combo box selection.

I tried this:

Please Login or Register  to view this content.

However I get an error saying that there are too many arguments for the range. So I need to figure out how to select cells L,M,N,O,P for the selected row based on the combo box, delete them, and shift the cells below up.

Does anyone know how I can do this?

Thanks

[snb]

Please Login or Register  to view this content.

[Leith Ross]

Hello Smurlos,

The comma in a Range is used to demark the start of the range and the end of the range. You get the error because there is more than one comma. You can use multiple commas only if they are inside quotation marks.

Please Login or Register  to view this content.

[Smurlos]

Hello Smurlos,

The comma in a Range is used to demark the start of the range and the end of the range. You get the error because there is more than one comma. You can use multiple commas only if they are inside quotation marks.

Please Login or Register  to view this content.

Hi Leith, I tried this but I get "run time error 1004 - select method of range class failed" :-(

Thanks
Carl

[Smurlos]

Hi guys, sorry I've sorted it! The macro before was selecting a different sheet, so the wrong sheet was selected! I've made it select the right sheet first and then execute the deletion of the cells and it works fine :-)

Thanks for your help Leith.

Regards
Carl

[Leith Ross]

Hello Carl,

The error tells me that the variable RowToDel is not a valid row number. Since you are adding 6 to the ListIndex property, you will never see a value less than 5. I don't see what is causing the error.

[snb]

If you only want to clear certain cells in a row use Excel's builtin facilities:

Please Login or Register  to view this content.

[Smurlos]

Hello Carl,

The error tells me that the variable RowToDel is not a valid row number. Since you are adding 6 to the ListIndex property, you will never see a value less than 5. I don't see what is causing the error.

It's all sorted now. The macro that I'd run previous to that was selecting the "Bookings" sheet, and those cells in question were part of a merged cell on that sheet I believe. The sheet I needed selected was "Setup" so I just inserted

Please Login or Register  to view this content.

Before and it now works perfectly, thanks.

If you only want to clear certain cells in a row use Excel's builtin facilities:

Please Login or Register  to view this content.

Hi snb, I didn't want to just 'clear contents' i needed the actual rows removed as it was affecting the list range - when I selected another user after deletion, it was taking data from the wrong rows. This is however all sorted now - thank you anyway.

Regards
Carl