Excel VBA - Macro to Filter Column if Contains Any Values from Range of Cells

JonEChavez

New Member
Joined
Jun 23, 2021
Messages
2
Office Version
  1. 365
Hello All, I've scoured the site, but first post, so hopefully I'm clear in the Q description.

I've got a worksheet, A-H, 1-100. In Columns C,D,E, there are singular, non-unique values. In Column G, there's a string of values which may contain one or more of the values from C,D,E.

I'm trying to put together a macro that -Prompts the user to select cells/range in columns C,D,E -Filters G for any rows that contain those values.

In my research, I found immediately the limitations of Advanced Filters. I started learning script writing just a week ago or so and am so far able to prompt the user to select cell, take the values and drop them to a cell in the format [="XX" or "XX" or "XX" ...] (where X's are the values from the selected C,D,E range).

I can't for the life of me figure out how to get row G to filter for rows containing these values. I think I may be on the wrong track with the data string as well, but any input would be much appreciated!! (nearly 100hrs already put into creating the database).
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I can't seem to delete or edit the post- so hoping to add some clarification as requested on another site:
I've got a worksheet, A-H, 1-100. In Columns C,D,E, there are singular, non-unique values. In Column G, there's a string of values which may contain one or more of the values from C,D,E.

I'm trying to put together a macro that: -Prompts the user to select cells/range in columns C,D,E -Filters G for any rows that contain those values.

Currently method attempt:

  • On button click, prompt user to select cells/range
  • Format Range into "="XX" or "XX"...
  • Drop string into a cell
  • Back in the Worksheet, the user would use the cell as a criteria for Advanced Filetering.
I've been looking all over different forums and can't find a solution for this. I'm pretty green in scripting, but am so far able to prompt the user to select cell, take the values and drop them to a cell in the format [XX" or "XX" or "XX" ...] (where X's are the values from the selected C,D,E range)[out]. When I try to add the "="" to the front [oout], I get an error on run.

I don't know if this is the best method, or if I should approach from a different direction. Any input would help. Thanks

VBA Code:
Sub Button84_Click()
Dim rng As Range
Dim out As String
Dim oout As String
Set rng = Application.InputBox("Select All Characters in Oper", "Get Cells", Type:=8)
out = WorksheetFunction.TextJoin("*" & Chr(34) & " or " & Chr(34) & "*", True, rng)
oout = WorksheetFunction.Concat(Chr(61) & Chr(34) & "*", out, "*" & Chr(34))
Debug.Print oout
Range("V1").Value = oout
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top