Find value from combo, clear from range and re-sort

[Jaz05]

Ok...I've searched the board looking for a similar quesiton on this issue, but cannot seem to find the answer, so here goes:

From the Combobox, the user can select a name from the Range.
The entire row containing the name selected is to be cleared from the Range and then the Range is to be re-sorted alphabetically, when the user selects 'OK'.

Apple 20
Banana 30
Pear 40
Strawberry 50

When Pear is selected, it is cleared from the range and the Range is now

Apple 20
Banana 30
Strawberry 50

Thanks.

 

[Damon Ostrander]

Hi Jaz05,

I'm not completely sure from your posting what you mean by "clear from the range". Do you want to eliminate the cells from the range, which would reduce the number of rows in the range? Or do you want to consolidate the data into contiguous rows, leaving an additional blank row at the end each time an item it deleted?

Also, is the range a Named range? (would probably be easier to delete a row from the range if it were).

Finally, it would appear that the list is already sorted, so doesn't need to be re-sorted when the row is deleted. Does it start out sorted as in your example?

Damon

 

[Jaz05]

Thanks for this Damon,

I thought that I made this a bit obscure!

Here goes:

The UserForm is "FrmRemoveEmployee"
The RowSource used in the Combobox is Range("Full_Name").

When the name is chosen ("OK"), all details on the employee's line is to be removed (i.e Employee Name, Employee #, Address, Department, Manager etc etc)

The Employee's line can be deleted (entire row), so that all the employee's below move up, therefore sorting is probably not required.

The gist of it is:
* Select employeer record; and
* Delete Employee Record from Range.

Currently there are 40 staff members, but I have the Full_Name Range going from B5:L100 (to compensate any additional employee's being added to the list).

 

[Damon Ostrander]

Hi again Jaz05,

If you want to delete the entire row of the Full_Name range, the following code will suffice:

With ComboBox1
Range(.ListFillRange).Rows(.ListIndex + 1).Delete
End With

Simply add this to the OK button's Click event.


However, if you want to delete the entire row OF THE WORKSHEET that the particular item falls within, the code would be:

With ComboBox1
Range(.ListFillRange).Rows(.ListIndex + 1).EntireRow.Delete
End With

 

[Jaz05]

I think we are getting there,
however,

Range(.ListFillRange).
is giving me the following error: Method or data member not found (Error 461)

 

[Damon Ostrander]

Hi Jaz05,

Sorry . The code I gave you was for a Combobox embedded on a sheet (uses ListFillRange rather than RowSource). Here's the code for a combobox on a userform:

With ComboBox1
Range(.RowSource).Rows(.ListIndex + 1).Delete
End With

Damon