sample data.xlsHello Everyone!
I need help to create a macro to delete a row if a certain name is found in column F of sheet2. The problem is that there are over 37 names and the name format is Last name, First name the middle initial. A few of the names are even more challenging. Below are a few examples of the name format:
Rusk, Debra L
O'Donnell, Daniel P
Palmer-Smith, Victoria C
Weinstein, Elizabeth
Sequera Sanchez, Ivan D
Last edited by johnny04; 09-17-2012 at 11:06 AM.
it would be nice or better I should say is to upload a workbook with the data and show what rows would be deleted
Thanks,
Mike
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
Thank you Mike for replying. Unfortunately I can not upload the workbook due to patient information that it contains. Also the workbook does not contain all of the medical physician’s names that may appear in the future. Each day the report has different names.
You can just mock up and example with some data and the challenging data, Show how it excist on bothe sheets.
Okay, I have added the sample data to my first post. I have just added a few rows. The original has well over 3,000 rows. Let me know if there is anything else that you need. Thank you for helping me.
Ok see if this will do what you need?
Please Login or Register to view this content.
Mike, each sheet contains needed but different information. Basically I will copy and paste data from sheet 1 into sheet 2. From there I need to delete the rows based on a particular physicians name. So I do not have a range of names to reference and these reports change from day to day so the above macro will not work.
So you just want to delete 1 physicians Name from sheet1. You lost me? So working with your sample sheets what is to happen? Are you wanting to be able to select a name on sheet 1 and look on sheet 2 if that name is there, delete it from sheet1?
Basically only working from sheet2 there are over a 100 different kinds of physician names. Out of that I want to delete 37 or more specific ones and the row that is associated with that physician's name. So I need a macro that searches column "F" for names Rusk, Debra L, O'Donnell, Daniel P, Palmer-Smith, Victoria C, Weinstein, Elizabeth, Sequera Sanchez, Ivan D, ....ect (long list of names) and if found then delete entire row. Does that make sense? Sorry for the poor explanation
So the deleting of rows is to be done on sheet2?
Where is the Specific list located? Is my macro just backwards? Should I be deleting rows on sheet2 where there names are on sheet 1?Out of that I want to delete 37 or more specific ones
There is no list, in the excel document does there need to be one? I tried creating an array macro but it would not work with a long list of names or the format of some of the longer names such as the ones with a ' in it. An employee runs reports out of another system and places them into the excel spreadsheet. She will do this each day but she does not need all of the physicians so that is why I wanted to delete them. Does that make sense?
Please Login or Register to view this content.
See if this will work. I would have a list of names somewhere so that I didnt have to mess with the code anytime I wanted to make changes to the names
Please Login or Register to view this content.
Yeah I think that might be the best bet. That way I will be able to add/take away physicians when needed. Is there a way to have the physician list in another workbook title "ED Physicians Names" and have the macro look for the list on that workbook and not from a different sheet?
So did that code work what you were wanting? Yea there is a way. how many times will you be updating the list? If not that much why not just hide the sheet? Or from the VbaProject Window select the sheet and in the Properties set visible to xlsheetveryhidden if you dont want anyone to access the sheet.
This code did not work. What did I do wrong?
Please Login or Register to view this content.
Opps that was my bad I should off set that up to in case you wanted to work with a worksheet. Being were working/setting the array with a range in becomes a 2 dimensional array.
So change drNames(i) to drNames(i,1) .
Please Login or Register to view this content.
Also will the Doctor that you want to delete, Name be listed more then Once? If so will I will need to update the code.
Ok thanks but now I got a run-time error '9' subscript out of range on that same line of code. Not sure why though. I did a direct copy and paste of the physicians name on sheet3.
Yes the physician's name may appear over 5 times depending on how bad they are at documentation
This works for me.
sample data (1).xls
It worked for me too but it did not delete all of them.yes there are multiple rows with the same physician.Also will the Doctor that you want to delete, Name be listed more then Once? If so will I will need to update the code.
Yea I didnt get you response to there would be more Dr names before I posted the workbook. This code below should do what you need.
Please Login or Register to view this content.
Heres the workbook to delete more then one
sample data (2).xls
for some reason it didn't loop. It still just took out just one name
Nevermind that second one worked perfectly!! Can you make a slight modification to have the macro look to a different spreadsheet for the phsician list? This way any user will be able to run the macro. We have more then one person that will be using this macro. I really thank you for your great help!
Sorry took so long had some things to take care of. Let me know if you have any problems
You will need to update the Const at the top of the Sub. sFilePath to the Path of the file. sFileName to the name of the file. shPhysicians to the Name of the sheet that has the Physicians Names.
Please Login or Register to view this content.
Last edited by mike7952; 09-17-2012 at 03:18 PM.
haha!! success! Thank you very much for taking all this time to help me and work on this I really appreciate it a great deal. Is there any way for me to give you credit for this?
Click Thread Tools above your first post, select "Mark your thread as Solved". Also can click the star under my last post and leave a comment.
Also might want to recopy the code I made a change to it as soon I hit save and you may copied it before I updated it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks