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

Mister_J

New Member
Joined
Dec 14, 2017
Messages
20
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,219
Office Version
365
Platform
Windows
Not sure if it would work for you, but you could put the values in an input box like
Code:
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

New Member
Joined
Dec 14, 2017
Messages
20
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,219
Office Version
365
Platform
Windows
Ok, in that case try
Code:
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

New Member
Joined
Dec 14, 2017
Messages
20
That is it!!! Thank you! I really appreciate your help, this community never lets me down.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,219
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,100,064
Messages
5,472,257
Members
406,809
Latest member
haf19

This Week's Hot Topics

Top