how to delete empty rows

Hi there in sheet1 I have a "IF" formula that deletes information if it has a "YES" on the "loans" tab...

is it possible that if the information gets deleted to have the rows do the same??? that way everything is grouped together???

The formula does NOT delete the data, if just displays a null value in the cells if the value in column F on the Loans tab is set to "Yes".

To delete the rows, you'd have to use VBA and a worksheet Change event to monitor the value in Column F. However, you'd have to change the formulae to something like a VLOOKUP rather than a simple link. Otherwise, the rows would get out of synch and it could get very messy. And there's no going back from deleting a row using VBA as the undo button won't work.

The other option is to hide the rows rather than delete them. That would maintain the integrity but make the display a little tidier.

Good luck if you pursue this approach.

Regards

If you look at the formula as you drag it across the columns, you will see that it is changing. You always only blank the *current* cell, if the cell "Loans!F3" is "yes". Otherwise, it copies the single cell which is the first in the range.

Anyway, if you want to HIDE the rows, you could use the following Worksheet Change event code:

Please Login or Register  to view this content.

Regards