Filtering macro

[ICSA_Perez]

Hi everyone,

I would like to automate our prioritization process.

At the moment I have 2 macro buttons but maybe it could be done in only 1.

The process works like this:

• I have plates that are cut on NC machines.
• When the cutting program is finished I fill the "Liberación" column with "Cortado". And I filter by empty cells to hide the row of the program that was cut.
• Each day I fill this sheet with more programs and prioritize them with increasing numbers. Then filter from lowest to highest.

When I press the button "Liberación" I would like that:

• The cells that indicate "Cortado" are hidden.
• The priority number of the hidden row is deleted or changed to 0.
• Adjust or subtract 1 from the other priority numbers to always have "1" at the top of the priority column.

As of now I don't know how to do the last point (adjusting the values to always have the 1 on top).

Thank you for your time.

Best Regards

[#2]

@ICSA_Perez 

To achieve the desired functionality, you can modify your existing "Liberación" macro to include adjusting the priority numbers.

Here is an example of how you can implement it:

Vba code (untested):

Sub Liberación()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("YourSheetName") ' Replace "YourSheetName" with the actual sheet name
    
    ' Filter and hide the rows with "Cortado" in the "Liberación" column
    ws.AutoFilterMode = False
    ws.Range("A1").AutoFilter Field:=4, Criteria1:="Cortado"
    ws.Rows.Hidden = False
    ws.AutoFilter.Range.Offset(1).EntireRow.Hidden = True
    
    ' Update the priority numbers
    Dim lastRow As Long
    Dim rngPriority As Range
    Dim cell As Range
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    Set rngPriority = ws.Range("A2:A" & lastRow) ' Assuming priority numbers are in column A starting from row 2
    
    For Each cell In rngPriority
        If Not cell.EntireRow.Hidden Then
            cell.Value = cell.Value - WorksheetFunction.CountIf(ws.Range("A2:A" & cell.Row - 1), 0)
        End If
    Next cell
    
    ' Clear the filter
    ws.AutoFilterMode = False
End Sub

Replace "YourSheetName" with the actual name of your sheet in the

Set ws = ThisWorkbook.Sheets("YourSheetName") line.

Here is how the modified macro works:

1. It filters the "Liberación" column to show only the rows where the value is not "Cortado" and hides the rows where the value is "Cortado".
2. It updates the priority numbers by subtracting the count of cells above that have a priority of 0 (assuming the priority numbers start from row 2 in column A).
3. It clears the filter to show all the rows again.

With this modification, the priority numbers will be adjusted to always have "1" at the top after hiding the rows with "Cortado" and filtering by priority.

Make sure to adjust the range references and column numbers in the code based on your actual setup.

Remember to backup your Excel file before running the macro, as it modifies the data in your sheet.

The text, steps and functions were created with the help of AI. To be sure that the code is behaving the way you want, it would be helpful to have the file (without any sensitive data) to run tests on rather than reconstructing one yourself. Information such as Excel version, file extension, operating system, storage medium would be of additional help.

My answers are voluntary and without guarantee!

Hope this will help you.

[#3]

Hi @NikolinoDE ,

Thank you for your help !

I'll try it out to see what goes.

Best Regards