using formula delete selection

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Using formula to delete selection of non-empty rows

Using formula to delete selection of non-empty rows

resolvedResolved · Medium Priority · Version 2003

Linda has attended:
Excel Advanced course
Excel VBA Intro Intermediate course

Using formula to delete selection of non-empty rows

Hi there, I have a big table and each month I have to delete out some old lines and send it to clients. I want to know if there is a way to "say" to excel "if this cell is less than x THEN delete row." Any suggestions?

RE: using formula to delete selection of non-empty rows

Hi Linda,

Thank you for the post, in response, without knowing exactly how your table works I have created a little scenario, hopefully you can adapt this to your problem:

For example; Cells A1:A100 have the data that you wish to check. Now to locate the "less than x" entries to be deleted, I would use the following formula in cell B1 ... =--(IF(A1<"x",0,"")) (where "x" is your criteria)

and I then would copy this down to cells B2:B100 (double click on the fill handle in B1)

Then I would select cells C1:C100, and action the following command; EDIT|Goto|Special|Formulas|Errors and the cells showing #VALUE errors would be selected.

(Note this selection would be easier if the range was named.)

Then action; Edit|Delete|Entire row

You might consider recording all that lot into a macro, then you have it automated:

I hope that has helped, regards Pete


 

Excel tip:

Random Numbers

Type =RAND()*200 to generate a number between 1 and 200.
Use the fill handle to drag down and populate as many cells as you'd like with random numbers.

View all Excel hints and tips


Server loaded in 0.1 secs.