Selecting and deleting rows based on content

[X82]

I am trying to automate a report I need for work.
Basically, I take a worksheet with 100 rows of information (6 columns) and in the final column there is information on a network location. I only have to deal with the rows which contain a certain 5/6 locations. Currently, I order the data by the location column and then delete manually every row which does not apply. This will slim down the rows from about 100 to less than 20. Each week the report changes by the amount of locations, however the location names I need to look for never change.

Here is an example.
In the example, anything marked as Admin in the location needs to be kept and personnel also. No matter if its at the start, middle or end of the location.
Every other row can be binned.

Any ideas?

[FDibbins]

There seems to be a problem with your upload, can you try again?

OK, I just saw that it was a link, not an upload. Please upload your workbook to the forum. Not all members are able - or willing - to access file-hosting sites

[xladept]

Hi X,

Try this:

Please Login or Register  to view this content.

[MarvinP]

Hi X82,

This looks like an Advanced Filter question/answer to me. Read:

http://www.excelfunctions.net/ExcelAdvancedFilter.html

and see if using Excel's Advanced Filter tool makes your problem go away.

[X82]

Hi X,

Try this:

Please Login or Register  to view this content.

Wow that's great.
Two things.
How can I add to the list of words it is looking for? If I need to add 10 more locations for example.
My scripting knowledge is limited.

Also, how can I used this with another macro?
For example, I have recorded macro which creates the file from a HTML location first, formats the sheet, removes a few rows from the top and then I want it to run this after. I assume this is easy to do?
Again, thanks, that worked perfect.

Hi X82,

This looks like an Advanced Filter question/answer to me. Read:

http://www.excelfunctions.net/ExcelAdvancedFilter.html

and see if using Excel's Advanced Filter tool makes your problem go away.

This looks interesting. I will look into this.

[Greg M]

Hi there,

You might find the attached workbook useful - it will allow you to select (from a dropdown list) all of the Location values which you wish to retain. Multiple Location values can be selected if required.

Any future changes to the layout of your worksheet can be accommodated by changing the appropriate values in the following statements:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[X82]

Thanks for the file Greg. While it works great at home, transporting a macro heavy sheet to work is a no no it seems. My works email quarantines it and I have no ability to use it.
Only raw code I can email myself. but I do appreciate the effort truly.

[Greg M]

Hi again,

Thanks for your feedback - glad you found the workbook interesting.

As far as "getting it past the sentries" is concerned, have you tried exporting the various VBA CodeModules to your home hard drive, emailing those exported versions (as attachments) to your work address, and then importing them into a workbook there?

Just a thought

In an extreme situation, you could just copy the various CodeModules to NotePad .txt files, email them to your work address, and then copy and paste at that end. You'd have to recreate the UserForm layout at work, but that shouldn't be too difficult.

Best regards,

Greg M

[X82]

Alas, I tried emailed the form files to me (after trying to be smart and exporting them) but that message was blocked.
I am now at work with the module code, but no clue how to make this form work with it.
Any ideas how to get this made easily?

[Greg M]

Hi again,

Creating the UserForm shouldn't be too involved - it contains only four controls.

In the VBA Editor click on Insert > UserForm on the main menubar - this will create a new "empty" UserForm

At the top of the Properties pane (click on F4 if it's not visible) you'll see a box for the (Name) Property - enter F01_Locations in this box

On the Toolbox menu (View > Toolbox if it's not visible) you'll see a control for creating a label (the second button on my system) - use this to create the label at the top of the form. Name the label lblInstructions in the Properties window, and enter "Select those Locations which should NOT be deleted" as its caption.

Use the Toolbox menu (the fifth button on my system) to create a ListBox on the UserForm. Name this ListBox lstLocations in the Properties window.

Use the Toolbox menu (the tenth button on my system) to create a CommandButton on the UserForm - in the Properties window, name this CommandButton btnOK, and enter OK as the value of its Caption.

As before, create another CommandButton on the UserForm and assign it a Name of btnCancel and a Caption of Cancel.

If you're feeling adventurous, use the Properties window to change the colours of the ForeColor properties of the above buttons to Green and Red respectively, to assign Accelerator values of O and n respectively, and to change the font of the OK button to Bold.

Now, right-click on the body of the UserForm itself and select ViewCode from the PopUp menu - this will open the VBA CodeModule for the UserForm.

Delete any pre-generated code (probably only 2-3 lines) from the CodeModule and then copy and paste the contents of the equivalent CodeModule for my UserForm into the CodeModule for your new UserForm.

I think the above steps should enable you to create your own "work-friendly" version of the UserForm.

Hope the above helps - please let me know how you get on or if you need any further help from my end.

Best regards,

Greg M


P. S. It's just occurred to me that you might not have copied the text from the UserForm CodeModule into a Notepad file, and that you therefore do not have it available to you at work right now, so I'm including that code below - hope it helps!

Please Login or Register  to view this content.

[X82]

Fantastic.
Thank you for such an informative post.
One thing however, dumb question, how do I run it?
Ideally I want to run it from a button in the toolbar, since the document itself will be freshly generated each week.
I've looked in the macros area and there is nothing there for this form.

[Greg M]

Hi again,

There is no such thing as a dumb question - by definition, questions are difficult if you don't know the answer, and are easy if you do!

As far as running the routine is concerned, the cheap and cheerful solution is to press Alt + F8 (when you're in the main Excel window - i.e. not in the VBA Editor window) and to select "RemoveUnselectedLocations" from the dropdown list of available macros.

Regarding your statement that "the document itself will be freshly generated each week", do you mean that an entirely new workbook will be produced each week? If this is the case, you'll have to go through the entire process of creating the UserForm, copying the VBA CodeModules each time - probably not what you have in mind!

To overcome this it should be possible to write some code which will copy the data from the weekly workbook into our "Master" workbook and to run the code from there.

In general, it's far easier to create controls (e.g. CommandButtons) on a worksheet than on the Ribbon (main menu), so I'd suggest that approach first of all.

Hope this helps - please keep me informed about how it's working out for you.

Best regards,

Greg M

[X82]

That macro does not appear in the list.
I've pasted your code into the form code and it doesn't show up in the macro list. I pasted it in a module instead and it still doesn't show up at all.
Am I missing something?

[Greg M]

Hi again,

The routine is declared as being of type Public, so it definitely SHOULD show up in the dropdown list.

Start from square one with a new empty workbook
Open the VBA Editor, and click on Insert >> Module on the MenuBar
In the new CodeModule, insert the following code:

Please Login or Register  to view this content.

Return to the main Excel window, press Alt+F8, and see if the "MyRoutine" macro is listed in the dropdown list.

I know I can't email you a workbook which contains macros, but if you can email me your version of the workbook I might be able to see what the problem is. If you can send outgoing emails with workbook attachments, let me know and I'll send you my email address in a private message.

Regards,

Greg M

[X82]

Hi Greg

The MyRoutine script works fine when I test it. Unless I am going mad, but surely the above long code should contain the phrase RemoveUnselectedLocations, for it to show up in the macro list?
But it doesn't. Unless I have missed some code somewhere. As far as I can see, the code doesn't contain a public sub anyway, only private. Am I miss-reading something here?

Again, this is the code I am using for the form code:

Please Login or Register  to view this content.

[Greg M]

Hi again,

Maybe we're getting somewhere now!

Your workbook should contain TWO CodeModules.

The first CodeModule is the one we've posted on here, and is the CodeModule associated with the UserForm you created this morning.

The second CodeModule is a general one, and is created in the same way that you created the one for the "MyRoutine" test. Create a new CodeModule (in your "real" workbook) and paste the following code into it:

Please Login or Register  to view this content.

If all goes according to plan, you should now be able to see the RemoveUnselectedLocations routine in the dropdown list shown when you press Alt+F8 in the main Excel window.


Here's hoping - please keep me posted!

Best regards,

Greg M

[X82]

Thanks Greg.
Really do appreciate the support thus far, surprised you haven't had enough by now!
I've ran the script but it errored with "Subscript out of range" pointing me to this line:
Set wks = Worksheets(msSHEET_NAME)

I am not sure if I mentioned, but is the column where the location is important? Sometimes when compiling this sheet, the location will change from row K to J or even to L. Also, sometimes the location can be split into multiple column, so rows J,K and L will contain location information. Does this throw a large spanner in things?

[Greg M]

Hi again,

Make sure that the worksheet which contains the data has the same name as that specified in the following statement:

Please Login or Register  to view this content.

You can name it whatever you like as long as the above statement contains whatever name you've chosen.



As far as the location column is concerned, an inconsistency certainly complicates things, but a solution can almost certainly be found. For the moment, let's concentrate on seeing if we can get the current version working, and we can turn our attention to the location columns afterwards.

Keep me posted!

Regards,

Greg M

[X82]

That works perfectly, to a point of course.
Because some of the data is in another column that is an issue, but to be fair, that is my layout which I can jig around.
Seems to be working good thus far though, truly great work.
Inspires me to think what else Excel can do with forms and such.

[Greg M]

Hi again,

Great! Many thanks for the prompt feedback.

Glad to hear that at least we've overcome the first hurdle

Do you want to upload / email me the possible alternative layouts for your worksheet? If I can see what variations need to be catered for I may be able to write some appropriate code.

Best regards,

Greg M

[Greg M]

Inspires me to think what else Excel can do with forms and such.

Indeed! It is an incredibly powerful and versatile application, and well worth mastering in as much detail as you can manage.

You'll get a real buzz when your friends/colleagues say "I never knew Excel could do THAT!!!"

Best regards,

Greg M

[X82]

It is indeed going smoothly. One thing I would query, the form box which appears when I run the macro, is there a way of setting it to as well as clicking on each, I can shift click to select multiple?
Not a major deal, just would save those precious seconds.

[Greg M]

Hi again,

There is indeed a way!

In the following routine (in the VBA CodeModule for the UserForm):

Please Login or Register  to view this content.

just change the highlighted value as shown below:

Please Login or Register  to view this content.

Let me know if there's anything else you need.

Best regards,

Greg M

[X82]

Perfect Greg. Brilliant stuff.
The end game for me is a sheet which will do my entire report for me!
But that is a mission for another day I feel.
Thanks again for your help. Much appreciated.

[Greg M]

Hi again,

Ok on that, you're very welcome.

Many thanks for all of your feedback - I'm very pleased that I was able to help.

Best regards,

Greg M