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???
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???
Last edited by jgomez; 02-02-2011 at 03:59 PM.
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
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I alslo wonder why you are using a range in =IF(Loans!F4="yes","",Loans!C4:H4)
I think =IF(Loans!F4="yes","",Loans!C4) would suffice
well if i have :=IF(Loans!F4="yes","",Loans!C4 <--- that only deletes that part not the whole row like I want...
TM you have a good point, I thought I would have to go the rought you said but was hoping that I wouldn't. Since i'm just doing this on the side for someone I don't think I will... Thanks guys.
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
An easy way to do it is to Edit---->Go to----> special-----> Blank
Excel will highlight all the blank rows, then you right click and delete.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks