Find value and delete then shift cells up

[sharky12345]

Can someone please show me how to find a value in a range, (in this case E2:E20), and delete the value then shift the cells below it up?

I want to avoid deleting the whole row because the is other data within the sheet, and for these purposes the value to be found will be a textbox value on a userform.

 

[Rick Rothstein]

Can someone please show me how to find a value in a range, (in this case E2:E20), and delete the value then shift the cells below it up?

I want to avoid deleting the whole row because the is other data within the sheet, and for these purposes the value to be found will be a textbox value on a userform.

This line of code in whatever UserForm event procedure you are using to execute it will find and delete the value contained in a textbox named TextBox1 from the indicated range...

Range("E2:E20").Find(TextBox1.Value, , xlValues, xlWhole, , ,False).Delete xlShiftUp

Note that I used xlWhole meaning the value being searched for will be the only text in the cell containing it; however, if that text could be in the cell along with other text, then you would need to change it to xlPart.

 

[sharky12345]

Perfect - many thanks Rick.