Delete rows based on cell content

Morning all,

I was hoping to automate some manual clean up to a weekly scheduling task performed by several people in our plant. Essentially I would like to delete all rows 2:2500 that does not pertain to the craft working with the file. There needs to be a setup for single craft and multiple craft. The craft work center is listed in B:B.

Ex. 1 would be a single craft.
Delete any row in range 2:2500 that does not contain (ELECTRIC) in colum B.

Ex. 2 would be a multiple craft
Delete any row in range 2:2500 that does not contain (EQ OPER) or (HARD TR) or (SOFT TR)

Hopefully this is a fairly easy one for the VBA gurus out there.

The help is always appreciated.

Hello scaffdog845,

It would be easier to answer your questions with a sample workbook. Can you post one?

Sure thing Leith.

One of the reasons I was looking to do this is because we currently have several users who are supposed to go into a workbook containing multiple sheets and copy information for their schedule to a new workbook then exit the master without saving. There has been a few times when users have manipulate/deleted data and saved the master causing a lot of confusion and problems. The attached book is the result of the following code which is extracting a single sheet to a new workbook.

Please Login or Register  to view this content.

The attached workbook has three sheets to show the example only. The goal is to have only one sheet based on the craft(s) that are running it. The sheet (1 week schedule by operation) is a data dump from SAP and can be upto ~2500 lines.

For a single craft example I choose (ELECTRIC). Once the code runs, the sheet (1 week schedule by operation) should look like the (ELECTRIC) sheet.

For the multi craft example I choose (EG OPER, HARD TR, SOFT TR). Once the code runs, the sheet (1 week schedule by operation) should look like the (EG OPER, HARD TR, SOFT TR) sheet.

I think I could mutle through all the minute changes with some sample code for a single and multi example. The end result would be form control buttons to click on based on the crafts being managed.

Hello scaffdog845,

I can create a VBA UserForm that will list all the crafts in column. You can then select either one or more and produce the individual sheets. Would that work for you?

Originally Posted by Leith Ross

Hello scaffdog845,

I can create a VBA UserForm that will list all the crafts in column. You can then select either one or more and produce the individual sheets. Would that work for you?

That would be GREAT, and greatly appreciated!

Hello scaffdog845,

This was an interesting project. I created a UserForm that allows you add a new sheet, delete a sheet, and update all sheets. The form is displayed as a window that can be minimized and restored. The close box is disabled so the form is available all the time. A new sheet has been added and is hidden called "Blank Template". This is used is creating new worksheets. The worksheet name is created by selecting the one or more craft centers and then clicking the "Add Sheet" button. This prevents misspelling of the worksheet name and insures the worksheet names remain consistent. Multiple craft names are separated by a comma and a space. Each selected craft is added to the new sheet from the master schedule. Try this out and let me know if anything needs to be changed.

Leith

Thank you very much for the help. It sounds great, unfortunately I won't be able to look at it until tomorrow in the AM. I'll make sure to let you know how it works out!

THANKS

Leith,

Thanks so much for the effort so far. Everything looks great. I just one question.

In the following code, I believe you are setting the worksheet name based on craft selections, which works great until it exceeds 31 characters. Is it possible to force a truncation to 31 characters?

Please Login or Register  to view this content.

Thanks again for everything.

Leith

Beside the sheet name character limitation in my last post, I am having an issue after importing your modules into a copy of the master sheet template. I like your form so much that I decided to abandon the idea of creating seperate files. I thought the individual sheet creation may be good enough to keep our users out of trouble.

When working with the form in the master workbook, I am receiving the following two errors when trying to create new craft schedule sheets.

A formula or sheet you want to move or copy contains the name 'TEST0', which already exists on the destination worksheet. Do you want to use this version of the name?

A formula or sheet you want to move or copy contains the name 'TESTKEYS', which already exists on the destination worksheet. Do you want to use this version of the name?

I've attached the workbook with your imported modules.

Any idea why the error messages are appearing? If I answer YES to each the sheet is created. If I answer NO I seem to be locked into entering something that seems to hang up.

Thanks

Hello scaffdog845,

I downloaded the new workbook. After looking it over, I'm not sure what you want to do now. Now you have a 1 week and a 3 week schedule and a new sheet "BY operation". This is very different from the first workbook. You need to explain how this setup works so I can make the UserForm work correctly.

Leith,

Those other sheets have nothing to do with what I am trying to accomplish. They are populated once and have no need for manipulation by anyone in the organization. Sorry for any unneeded confusion!

Thanks

Hello scaffdog845,

I can add validation to limit the worksheet name to 31 characters. What I don't understand is the error. The macro checks if a sheet is already present in the workbook. Did you change some of the code?

Originally Posted by Leith Ross

Hello scaffdog845,

I can add validation to limit the worksheet name to 31 characters. What I don't understand is the error. The macro checks if a sheet is already present in the workbook. Did you change some of the code?

Leith,

I did not change any of the code, except to remark out launching the form on startup. I simply exported the modules from your spreadsheet, then imported them into the master sheet I had. The code ran without errors in the sheet you provided me with originally.

Thanks

Leith

This workbook should only ahve two named ranges (Print_Area and Print_Titles) For some reason, after using the form to create new sheets additional named ranges are created matching the names in the error messages. These named ranges have a #REF! error as the value. I'm not sure why this is happening. I deleted the TEST0 and TESTKEYS named ranges and have been testing with it and I can not get the error messages to repeat. That may be a mute issue as of now.

I really appreciate the effort!!!

Originally Posted by Leith Ross

Hello scaffdog845,

I can add validation to limit the worksheet name to 31 characters. What I don't understand is the error. The macro checks if a sheet is already present in the workbook. Did you change some of the code?

Morning Leith, any luck with the data validation? The error has not returned since I deleted the new named ranges that were created the first time the form was used.

Thanks

Bump. No response

Hello scaffdog845,

Here is the updated macro that will limit new worksheet names to a maximum of 31 characters.

Please Login or Register  to view this content.

Originally Posted by Leith Ross

Hello scaffdog845,

Here is the updated macro that will limit new worksheet names to a maximum of 31 characters.

Thanks Leith. It does indeed limit the sheet name to 31 characters, however the sheet only populates with the schedule for the workcenters which are fully listed on the sheet name. I appreciate all the effort thus far. I'll see if I can figure out a way to add in some code to populate a list or something somewhere on the sheet that will tell your code which workcenter information to bring over when you hit the update button.