Code to delete row's contents based on one blank cell


Posted by Tom Urtis on July 07, 2001 10:06 AM

Hello --

In Excel 2000 I maintain a dynamic range of 50 columns and 10,000 rows to keep track of district sales.

Column A contains a unique "Sale ID Number". Sometimes after a sale is recorded it is later rescinded by the purchaser, so it needs to be deleted from the range.

I have a macro that allows the user to input the Sale ID Number, and then search column A of that range to find the cell containing that number, and then delete the contents in that cell. I am not sure what code to add that would:

(1) Delete the useless contents of the entire row belonging to the just-deleted rescinded Sale ID Number,

(2) How to ensure that a run time error will not happen if the user enters a Sale ID Number that is not included in the range in the first place.

Any suggestions??

TIA.

Tom



Posted by Scott S on July 07, 2001 11:39 AM

You could add "Selection.EntireRow.Delete" to your code, and it would delete out the entire row of the selected cell. However, if your code leaves the entire column selected, it will will delete all rows, so be careful. As far as handling errors, or when the desired cell is not found, it should be able to be handled with an Error Handler. I would need to see your macro code to suggest one though.