How to use a macro to select value criteria from a table rather than manually inputting
Category: [Excel] Demo Available
How to use a macro to select value criteria from a table rather than manually inputting
Requirement:
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
Rows(x).Delete
End If
Next x
End Sub
Solution:
Code:
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.