How to use a macro to select value criteria from a table rather than manually inputting

< Back to Search results

 Category: [Excel]  Demo Available 

How to use a macro to select value criteria from a table rather than manually inputting




The user has this macro below which might not be the fastest but seems to work pretty well, but needs some help on having it pick up value criteria from a table rather than manually inputting it as the user has currently 20 that the user needs to search for and delete and might have some more so ideally something where it can pick up a larger list and then search for true values within the list that would be ideal.


Currently the user has the list of values the user wants to search for to delete rows that include that value, they are on K16 to K35 but I might expand the list so if its possible to do up to for e.g. K40 and search for true values in the list that would be ideal.


The user also has the list on a different sheet caller Drivers and the dataset  which the user wants to remove values from is called Import Data, the user wants to be able to run the macro from the Drivers page from a button so the user also need a line adding to do the search on the Import Data page rather than the active sheet.



Sub RemoveCode()

Dim x As Long, lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, 6).Value = "XXXX" Then
End If
Next x

End Sub




Sub RemoveAllCodes()
Dim lastrow As Long, lastrowK As Long, c As Range, rngK As Range
Dim res As Variant
lastrow = Sheets("Import Data").Cells(Rows.Count, 1).End(xlUp).row
lastrowK = Sheets("Drivers").Cells(Rows.Count, 11).End(xlUp).row
Set rngK = Sheets("Drivers").Range("K35:K" & lastrowK)

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    For Each c In rngK
    res = Application.Match(c.Value, Sheets("Import Data").Range("F1:F" & lastrow), 0)
    If Not IsError(res) Then
            Sheets("Import Data").AutoFilterMode = False
        ' ''''''''''MAKE SURE TO ADJUST THE RANGE TO YOUR ACTUAL SITUATION '''''''''''''''''''''''''''
            With Sheets("Import Data").Range("A1:M" & lastrow)
              .AutoFilter Field:=6, Criteria1:="=" & c.Value
              .Offset(1, 0).Resize(lastrow - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End With
     End If
    Next c
' Back to Normal
Sheets("Import Data").AutoFilterMode = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox " All Codes have been Removed ..."
End Sub


Obtained from the OzGrid Help Forum.

Solution provided by Carim.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to format InputBox as date
How to input a row variable pertaining to all macros
How to find and replace based on list entered by user input
How to use cell content as input to a structured reference as part of a lookup function
How to COUNTIF using input cell as range depth


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)