Quick question about automatic row deletion (should be very quick and easy!!)
Hey all! So, I saw this sample code and I need to adapt it so that instead of seeing if a row is numeric or not, it's checking if it contains (anywhere in the cell) "REF." Basically, whenever a paste-link is broken, I need that row deleted.
I have a feeling this should be really simple, but I'm not sure of the syntax. Quick help is greatly appreciated!!!
Please Login or Register to view this content.
If you are referring to the broken cell reference which is #REF then the below should work. If you want to check for the actual letters "REF" then you are going to need something entirely different. I would test this on a copy of your workbook.
Please Login or Register to view this content.
This works great!
Once thing I'd like to do though... How can I get this code to run automatically whenever a sheet is deleted? My workbook has several sheets, and I need it to perform this action on the "Summary" Sheet. So I'm not sure how I can implement the code in a way that, whenever a worksheet is moved to another workbook or deleted, the workbook automatically shoots over to the "Summary" sheet, then performs this loop to find the broken cell references. Ideas?
Last edited by poptart141; 04-08-2013 at 03:15 PM.
...Anyone?
Okay, well anyway... I've done some tinkering on my own, and here's the code that I've come up with:
So, whenever a cell on the Summary sheet is selected, it automatically runs the code and clears out any rows containing a broken link. Woo! That's great and all. But is there a way that I can make Excel automatically switch to cell A1 on the Summary sheet when a sheet is deleted? I've looked everywhere and can't find an answer. This way, as soon as a sheet is deleted, it would select a cell on the Summary sheet, which would make it update and remove the broken links.Please Login or Register to view this content.
I've tried some things that sound like they would work in my head... Like If Sheets(Sheets.Count)-1 Then (run the code), or If ActiveWindow.ActiveSheet.Delete Then (run the code), etc. Can't get anything to work.
I would probably do it like this:
Please Login or Register to view this content.
Thanks for the reply. I copied and pasted that code into the Summary Sheet code, and it didn't do anything. Is there somewhere else I'm supposed to place it?
Still need help with this
So, I got this code to work with a button just fine. However, I'd like it to be automatic. As in, as soon as you select the Summary Sheet, it clears out any existing rows with broken links.
It doesn't go in the summary sheet code. The first two go under the workbook. The third should be on its own.I copied and pasted that code into the Summary Sheet code
You would use Worksheet_SelectionChange. It is not what you think it is. You would use Activate under the Summary Sheet.
Please Login or Register to view this content.
Last edited by stnkynts; 04-09-2013 at 10:35 AM.
Okay, you lost me. So are you saying to change:
Please Login or Register to view this content.
to:
?Please Login or Register to view this content.
Can you clarify what goes where a little bit? I haven't been doing VBA very long at all.
Last edited by poptart141; 04-09-2013 at 10:47 AM.
Ahhh, okay. Thanks! After tinkering with it a bit, I think I got it working. I'll keep messing with it and make sure I don't have any problems anywhere...
Seems like it's fine. Thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks