I have a spreadsheet where I have information in each month. When I click the cell with the name and hit the delete button it deletes the name in that month, moves everything up, but not for the months after. the months after it gives me a #ref error. Is there a way to delete information in one month excel will automatically delete the inforimation in that month and the months after it moving the information up so there is not a blank row? see attached.
Here is a macro that you can use to delete all rows that have #REF! in Column D.
As for making it automatic, it will take a real guru to figure out how to work it into a "Worksheet_SelectionChange".
Please Login or Register to view this content.
Hope this is helpful.
I have this working on each tab already. I just need to figure out how to get rid of the REF for the tabs after if they are there, even if they are left blank.
Why have a tab setup like you for each month and why delete the monthly data? You have over 1 million rows. Take advantage of them. Just add a date column to your data and use the filtering available in Excel to hide the months that you don't want to see. You will not be receiving #Ref errors that are due to the reference no longer existing and you will be able to produce reports that cover a period of time greater than 1 month.
Doing the above, you will no longer have need of macros and you will have a history of your data.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I would do that but where I work I need to make it simple for the staff. It is not easy for most to figure that out. It is easy for you and I but some will make things difficult.
Educate them! It isn't rocket science.
@ferriskenny Open up the attached workbook. Put cursor in any row in any sheet and click on the "Delete Row" button (or just run the macro)
The macro has been written in a slightly long-winded way so that it is clearer to understand what is going on.
What the macro does
Deletes details for a single patient in active sheet and in FUTURE months
(I assume that you do not want to delete prior months details)
How it does it
- asks for confirmation that user has his selection correct
- uses the "Match" function to find future records for that patient
(all patient names must be unique)
- updates arrays to capture sheet and row numbers
- provides feedback to user of future records found
- asks user to confirm deletion request
- loops through arrays and deletes required rows
Hope this gets you close - if it does, then we can build a simple "checker" for non uniques patient names.
Please Login or Register to view this content.
If a response has helped then please consider rating it by clicking on *Add Reputation below the post
When your issue has been resolved don't forget to mark the thread SOLVED (click Thread Tools at top of thread)
This works perfect. If I create a index in a new tab so the name cannot be accidentally be deleted by hitting a space button or a different letter, I keep getting a debug error because the name cells are locked. How would I fix?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks