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.

Sub Electric()

' Electric Macro


Dim pobjXLApp As Excel.Application   'Excel Application

Dim pobjMasterXLBook As Excel.Workbook    'Excel Workbook
Dim pobjMasterXLSheet As Excel.Worksheet   'Excel Worksheet

Dim pobjNewXLBook As Excel.Workbook    'Excel Workbook
Dim pobjNewXLSheet As Excel.Worksheet   'Excel Worksheet

Dim pstrHistoryFileLocation As String
Dim pstrFileNameData As String
Dim pstrNewXLbook As String
Dim pstrResponse As String


    
    Set pobjXLApp = Excel.Application
    'Set Master objects
        Set pobjMasterXLBook = ActiveWorkbook
        Set pobjMasterXLSheet = pobjMasterXLBook.Worksheets("1 week schedule by operation")
    
 
    
    'Opens a new workbook
        Set pobjNewXLBook = pobjXLApp.Workbooks.Add              'Creates new workbook
        Set pobjNewXLSheet = pobjNewXLBook.Worksheets(1)            'Runs Worksheet 1= First Sheet
    'Select the Master sheet and copy it to the history spreadsheet.
        'Copy the first sheet
        
        
        pobjMasterXLSheet.Copy pobjNewXLSheet
        Set pobjNewXLSheet = pobjNewXLBook.Worksheets("1 week schedule by operation")
        pobjNewXLSheet.Activate
       ' Cells.Select
       ' Selection.Copy
       ' Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
       '        xlNone, SkipBlanks:=False, Transpose:=False
       
       
       ' New for custom sort
           Range("B1").Select
    
    Range("A2:K2500").Select
    ActiveWorkbook.Worksheets("1 week schedule by operation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("1 week schedule by operation").Sort.SortFields.Add _
        Key:=Range("B2:B2500"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("1 week schedule by operation").Sort.SortFields.Add _
        Key:=Range("A2:A2500"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("1 week schedule by operation").Sort.SortFields.Add _
        Key:=Range("C2:C2500"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("1 week schedule by operation").Sort.SortFields.Add _
        Key:=Range("F2:F2500"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("1 week schedule by operation").Sort.SortFields.Add _
        Key:=Range("G2:G2500"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("1 week schedule by operation").Sort
        .SetRange Range("A1:K2500")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        Range("B1").Select
    End With
       
End Sub

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

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