fine name in column, delete entire record (Excel 2003 Win XP)

Topic

New Reply

Hi, Is there a subroutine out here that will allow me to use the find command to locate multiple occurences of a Name in a column, but then expand the selection to the entire row(s) so I can delete them en masse? Also, I’d like to use the same concept to find a name(s) in a column, but select all the entire record(s) so I can color-code them.
OR, better yet, is this a function in Excel and I just don’t know how to use it? Thanks for your help. Lana

Reply | Quote

Replies

See if the threads starting at post 562,687 or post 590,834 help. You can also search this forum for delete duplicate for other relevant threads.

Reply | Quote

Hi Hans, thanks for the tip. I was hoping you’d still be out there. I have copied the routines in those posts you suggested, but am a bit confused by them. I will try them when I get to work this morning. I’m not much interested in messing with duplicates, just want to do a find, then expand the selections found to the entire record. There is one here regarding color coding that I will try first and if that works perhaps I can amend it to include a delete command. I’ll let you know.
Lana
P.S. When I tried to edit this message it was overwriting rather than inserting, do you know how I can change that?

Reply | Quote

> P.S. When I tried to edit this message it was overwriting rather than inserting, do you know how I can change that?

Perhaps you accidentally pressed the Insert key, this toggles between insert and overwrite. (There is no Lounge-specific setting for this)

Reply | Quote

The attached routine will delete duplicated rows without looping. There are options for
delete all duplicated rows (Sam, Dave, Mary, Sam, John, Dave >> Mary, John)
keep first duplicated row, delete the rest (Sam, Dave, Mary, Sam, John, Dave >> Sam, Dave, Mary, John)
Delete all rows that match the name the user inputs
Keet the first row of the user input name, delete the other occurances.

For mass coloring, you could use Condiditonal Formatting.

Reply | Quote

Hi Mike,

Would you believe this help site is blocked at my work! So I am just getting home and took a look at these commands. I will try them at work tomorrow. I do have something working, but don’t quite know how I got there. Started with the delete dups coding Hans pointed me to and edited and tweaked ’til it worked. Odd thing is when I try to record a macro that does a find on something, then I select all those occurrences, and do a delete – entire row, it works beautifully, but the find command doesn’t record so I can’t embed it in my macro – I think that’s odd. Hope yours work for me. Thanks for responding.

Reply | Quote

Hi Mike and Hans,
Well, I had a big long message to post and the web page timed out. Good grief. At any rate, I have a pretty decent (although admittedly unsophisticated) macro recorded/coded to generate the report I need. However, I feel that I am still doing more manually than I need to. First, when I open the master file I immediately do a save as (I save it as the same title each time to make it easy) and then I have to respond to the ‘yes’ or ‘no’ do you want to overwrite the existing file question. How do I do that automatically?
Then, odd thing, when I do a sort, first on column H, then column A, it works beautifully. When I embed it into a macro I get error message 1004 – ‘This operation requires the merged cells to be identically sized.’ Well, to my knowledge I only have 3 cells merged and I particularly changed them today to be the same width. After I got home I thought maybe they mean vertically, although it would be weird and accidental if they weren’t the same height. Any way to correct that with code or at least to find the culprit? Funny it works immediately when I do it from the keyboard.
When the report is complete I would like to tell it to go to the last populated cell and do a Ctrl Shift Home to select all of the data, then do a set print. Can I then do a save, close both the macro and excel, or is that not possible from within the macro?
Not done yet, since I earlier find all of the names I don’t need and change them to abc, it seems silly that I then have to tell it to delete rows where it encounters abc (in response to the user input command).? Can’t I just embed that within the macro somehow? That would eliminate that manual step as well.
If these are easy fixes, please let me know. If not, that’s fine, I can certainly go with what I have and still create my report in a few minutes. Thanks for all your help.

Reply | Quote

Our server is being backed up every day between 23:00 and 01:00 UTC, and Woody’s Lounge is extremely slow for much of that time – sorry about that.

You can suppress warnings such as the overwrite warning by inserting a line

Application.DisplayAlerts = False

above the code that generates the warning. If you want to display the warnings again, use

Application.DisplayAlerts = True

Sorting a range with merged cells is problematic. I would avoid merged cells if possible.

To get the range from the first used cell to the last used cell, you can use ActiveSheet.UsedRange. If you want to print this range, you can use ActiveSheet.UsedRange.PrintOut.

To save the active workbook: ActiveWorkbook.Save.

To quit Excel (and hence end macro execution): Application.Quit.

Reply | Quote

Thanks Hans, I will add those fixes tomorrow. I really hate it that this site is blocked at work, it makes it very inefficient for getting help in a timely manner. BTW, that merged cells deal, the two columns in my file (H) and (A) are neither ones that contain merged cells. Should that matter, or is it affected by merged cells anywhere in the worksheet?

Lana

Reply | Quote

Merged cells anywhere in the range that you want to sort (even if you don’t sort on those specific columns) cause problems.

Reply | Quote

Okay then, I’ll try to eliminate them and see if works – Iprobably don’t really need that umbrella heading anyway. Thanks again. Simply can’t get rid of this overwrite thing so excuse my typing. Lana

Reply | Quote

Some time back Hans enlightened me about the following alternative to horizontally merged cells.

• Place data in the leftmost cell
• Select the range that should appear merged
• Pull down: Format > Cells (Ctrl +1)
• Select the Alignment tab
• Pull down: Horizontal > Center Across Selection
  [/list]

Reply | Quote

There are four functions in that routine.
If none of them works for you, the idea is to concoct a string (formulaString) such that the formula

spareColumn.FormulaR1C1 = "=1/(1-(" & formulaString & ")"

will return an error value for those rows that you want deleted.

Merged cells get in the way of so many procedures that I prefer CenterAcrossSelection, when possible.

Reply | Quote

I was going to try that center across selection tomorrow – nice to know that it will work. Also, Mike the routine you provided that asks for user input works just fine for me, that’s what I am currently using. But since I just have the one string to find and delete (abc), I thought I could do it automatically rather than with user input. I’ll see if I can tweak it tomorrow as well. Thanks to all of you.

Reply | Quote

Well, all, you’ve done it again. I now have a working macro that I run by clicking an icon on the toolbar. How neat is that? Hans, all of those quick little commands you gave me worked great, even the one that selects just the records with data – I was leery of that, but it works just fine. I’ll have to keep that in mind. As well, Don, that Center Across Selection is great, too, I must remember that! And Mike, kudos to you as well, all I had to do was replace the words UserString (or whatever) with “abc” and the macros works fine with no input from me – wonder why I couldn’t have figured that out yesterday. At any rate, this ends the thread – I am content. Thanks much.

Reply | Quote