Help fixing a Multi-Filtering Macro That Requires Letters and Numbers.

[Mister_J]

Quick Summary:
I have a macro that requires a column to contain numbers and letters in order to be run properly and I would like it to work either with just numbers, just letters, or both.

I found a thread a while back that had a macro request and a solution that was very similar to what I was looking for, so I was happy and decided not to post anything. I have been using it for a while now however, it has one feature that I have no idea how to fix which is why I am posting this. This macro was designed to allow filtering of multiple values in the column of your choice.

Example:
You have a column with 1-100. You insert a temporary column A and place all the values you want to see like 2,23,35,49,60,88,94. When you run the macro it asks you to type the column that has the values you would like to filter so you can type C (or any column letter) and hit enter and only the rows that contain the numbers you have listed in column A will be shown. Once you are done you can delete column A and your spreadsheet is back to normal.

The feature I would like to fix is that currently the filtering requires the column you want to filter to contain both letters and numbers it cannot be just numbers. If you have only numbers, they will not show up when filtered. The work around I have been using is to just add a letter to all the numbers and deleting it with find and replace when I’m done but if someone could help fix the macro to allow “just numbers, just letters, or both” to be filtered this macro would be perfect.

Thanks in advance to anyone willing to help, this is WAY above my understanding of VB code. If it is not possible I understand, at the very least maybe someone can benefit by getting this macro from this post like I did from the one I got it from.


Macro:

Sub FilterOnList()

Dim Ary As Variant
Dim col As String

col = InputBox("Enter the LETTER of the Column you wish to filter." & vbLf & _
"=================================" & vbLf & _
"It will NOT work on numeric ONLY values." & vbLf & _
"It has to be a combination of both. Ex. 82LSG1234")
If Len(col) = 0 Then Exit Sub

Ary = Application.Transpose(Range("A1", Range("A" & Rows.Count).End(xlUp)).Value)
With ActiveSheet
If .AutoFilterMode Then .AutoFilterMode = False
.Range("A1:J1").AutoFilter Asc(UCase(col)) - 64, Ary, xlFilterValues
End With

End Sub

 

[Fluff]

Not sure if it would work for you, but you could put the values in an input box like

Sub FilterOnList()

Dim Ary As Variant
Dim col As String

col = InputBox("Enter the LETTER of the Column you wish to filter." & vbLf & _
"=================================" & vbLf & _
"It will NOT work on numeric ONLY values." & vbLf & _
"It has to be a combination of both. Ex. 82LSG1234")
If Len(col) = 0 Then Exit Sub

Ary = Split(InputBox("Enter values separated by a comma"), ",")
With ActiveSheet
If .AutoFilterMode Then .AutoFilterMode = False
.Range("A1:J1").AutoFilter Asc(UCase(col)) - 64, Ary, xlFilterValues
End With

End Sub

 

[Mister_J]

So I will definitely be saving that, thank you! However, if possible I would like to keep the column A feature because the first thing I do is run the highlight duplicate command to confirm everything is there and no numbers are missing. Also I will leave the column A numbers there sometimes so I can pull the same list if it is needed.

 

[Fluff]

Ok, in that case try

Sub FilterOnList()

Dim Ary As Variant
Dim col As String
Dim i As Long

col = InputBox("Enter the LETTER of the Column you wish to filter." & vbLf & _
"=================================" & vbLf & _
"It will NOT work on numeric ONLY values." & vbLf & _
"It has to be a combination of both. Ex. 82LSG1234")
If Len(col) = 0 Then Exit Sub

Ary = Application.Transpose(Range("A1", Range("A" & Rows.Count).End(xlUp)).Value)
For i = 1 To UBound(Ary)
   Ary(i) = CStr(Ary(i))
Next i
With ActiveSheet
If .AutoFilterMode Then .AutoFilterMode = False
.Range("A1:J1").AutoFilter Asc(UCase(col)) - 64, Ary, xlFilterValues
End With

End Sub

 

[Mister_J]

That is it!!! Thank you! I really appreciate your help, this community never lets me down.

 

[Fluff]

You're welcome & thanks for the feedback