Forum Discussion

ICSA_Perez's avatar
ICSA_Perez
Copper Contributor
Jul 06, 2023

Filtering macro

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

excel
Macros and VBA
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jul 07, 2023

    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.

Resources