MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Delete a row in an "IF" function

Posted by Tiffany on November 09, 2001 11:25 AM

From a user side I need to know if there is a way to delete a whole row based on the results of an IF function. I have several balnk rows sandwiched between data that I need to quickly and easily remove. Is there a user function that will work for this purpose?


Posted by giacomo on November 09, 2001 12:00 PM

You can do a sort this will send the blank rows to the bottom.

Posted by Tiffany on November 09, 2001 12:17 PM

That would work, but some of the columns have data in them and others do not. If I sort to remove the blank rows, I will not be able to get the columns back with their original lines, since the 2nd and 3rd lines of each set do not contain any similar data as the first line. It it too time consuming to copy the first line of data into the 2nd and 3rd lines for the thousands of sets I have. Thanks for the suggestion, though. HELP!


Posted by JACK on November 09, 2001 3:39 PM

I dont know if i agree with the IF ?? What condition are you IFing to? I have afeeling you need to VBS this a formula will not delete as i have never known a formula or IF to delete anything, Formulas are text/number amenders or converters aor treansfereers, your need the power of VBA to do this for sure.

OK is the data inproted, and does these have rows that are compleaty enpty? if so if is useless and simple VBA script will remove enpty rows and shuffle up the data left.

If the rows have data but some blanks then whats the conditions to being deleted?

Next the code i guess

Posted by Gianni Rivera on November 09, 2001 4:05 PM

I don't have a clear understanding of how your data is arranged, but if you want to get back to the original sequence after sorting and deleting, then see if this works :-

1. Insert a new column and fill it with sequential numbers down to the last data row
2. Sort by whatever column(s) you wish
3. Delete the requred rows
4. Sort by the new column to get back to the original sequence