Retain a data value and remove other cell contents.

[Astronaut01]

Hello excel vba experts
Im quite new to excel vba and need to create a vba tool that can:
1. Search for all data value/s that has the "RTN000..." in column F. Some cells in column F has mulitple "RTNs..." and symbols and words.
2. Once found, it should copy that specific "RTN" together with all the row data, to a sheet i named Output.

Been trying to see if a use of wildcard would work but not sure how. Many thanks in advance for all your solutions.

 

[BSALV]

is that the VBA-version of doing by hand
* filter column F on "contains RTN000"
* copy the visible rows to sheet "output" ?

 

[Astronaut01]

is that the VBA-version of doing by hand
* filter column F on "contains RTN000"
* copy the visible rows to sheet "output" ?

Hi bsalv
You mean by recording macro?.. No if i understand. But im trying to create a vba code which uses an activeX button. The vba code should be able to just retain all RTNs in a cell.
Yes it should copy those rows containing RTNs found, to sheet output.
TIA

 

[Astronaut01]

I mean search for the RTNs, not retain.

 

[BSALV]

Sub Filteren()

     Set c = Sheets("dest").Range("a1")                         'destionation topleft
     c.Resize(1000, 10).ClearContents                           'clear

     With Sheets("mysheet").Range("$A$1").CurrentRegion
          .AutoFilter Field:=6, Criteria1:="=*RN000*"
          .SpecialCells(xlVisible).Copy c
     End With

End Sub

 

[Astronaut01]

hi bsalv
I tried your vba code and it is working but i think the code is returning all RTNs with three zeros in it. Is there a way to only return specific RTNs that I would put in my "RTN TO SEARCH" column in my "findap" sheet? Pls see my images. The steps would be:
1. In the "findap" sheet, i will enter specific RTNs under RTN TO SEARCH column, hit the Search RTN button... then step 2..
2. It then searches those RTNs from the Sheet1 which is the report file. Take note that there are multiple RTNs in some cells under column F "Value".
3. It should then output those specific RTNs in my "output" sheet.

Thanks in advance again bsalv. I am confident you will be able to help me with this one as it is almost there and just need to find the specific RTNs. Looking forward to your response.

 

[BSALV]

1st point is no problem, 2nd also, but the 1st combined with the 2nd is.
I saw no image of your data, perhaps later, 5 rows (without sensitive data).
In total how many rows is this ?

I think i already know how.
i assume the 1st column of your data is column A, what is the last column ?
Because i 'm adding an auxiliary column there.

 

[BSALV]

At the RHS, that green table are all your searchvalues.
The green range is a defined nama "Wanted"
The column H is that auxiliary column and is the 8th column of that table, that's the 8 in the VBA-code, you have to change that one to your situation.

If the formula in column H doesn't work, perhaps it's a matrixformula, in my 2021-version, i can't check that. So enter that with CTRL+SHIFT+Enter instead of a normal enter

Sub Filteren()

     Set c = Sheets("dest").Range("a1")                         'destionation topleft
     c.Resize(1000, 10).ClearContents                           'clear

     With Sheets("mysheet").Range("$A$1").CurrentRegion
          .AutoFilter 8, 1
          .SpecialCells(xlVisible).Copy c
          .AutoFilter
     End With

End Sub

Map1
	A	B	C	D	E	F	G	H	I	J	K
1	a	b	c	d	e	f	g	auxiliary			Wanted
2	A2	B2	C2	D2	E2	RTN0002	G2	0			RTN0006
3	A3	B3	C3	D3	E3	RTN0003	G3	0			RTN0009
4	A4	B4	C4	D4	E4	RTN0004	G4	0			RTN00012
5	A5	B5	C5	D5	E5	RTN0005	G5	0			RTN00015
6	A6	B6	C6	D6	E6	RTN0006	G6	1			RTN00018
7	A7	B7	C7	D7	E7	RTN0007	G7	0			RTN00021
8	A8	B8	C8	D8	E8	RTN0008	G8	0			RTN00024
9	A9	B9	C9	D9	E9	xxxxxxxxxxxxxxx RTN0009xxxxxxxxxxxRTN0001	G9	1			RTN00027
10	A10	B10	C10	D10	E10	RTN00010	G10	0			RTN00030
11	A11	B11	C11	D11	E11	RTN00011	G11	0
12	A12	B12	C12	D12	E12	RTN00012	G12	1
13	A13	B13	C13	D13	E13	RTN00013	G13	0
14	A14	B14	C14	D14	E14	RTN00014	G14	0
15	A15	B15	C15	D15	E15	RTN00015	G15	1
16	A16	B16	C16	D16	E16	RTN00016	G16	0
17	A17	B17	C17	D17	E17	RTN00017	G17	0
18	A18	B18	C18	D18	E18	RTN00018	G18	1
19	A19	B19	C19	D19	E19	RTN00019	G19	0
20	A20	B20	C20	D20	E20		G20	0
21	A21	B21	C21	D21	E21	RTN00021	G21	1
22	A22	B22	C22	D22	E22	RTN00022	G22	0
23	A23	B23	C23	D23	E23	RTN00023	G23	0
24	A24	B24	C24	D24	E24	RTN00024	G24	1
25	A25	B25	C25	D25	E25	RTN00025	G25	0
26	A26	B26	C26	D26	E26	RTN00026	G26	0
27	A27	B27	C27	D27	E27	RTN00027	G27	1
28	A28	B28	C28	D28	E28	RTN00028	G28	0
29	A29	B29	C29	D29	E29	RTN00029	G29	0
MySheet

Cell Formulas
Range	Formula
A2:E29,G2:G29	A2	=CHAR(COLUMN()+64)&ROW()
H2:H29	H2	=MAX(--ISNUMBER(SEARCH(Wanted,F2)))

Named Ranges
Name	Refers To	Cells
Wanted	=TBL_Wanted[Wanted]	H2:H29

 

[Astronaut01]

Hi bsalv
The code works. However, i'd like to display the result on my "output" sheet. Problem is, my "findap" sheet is only allowing me to put 2 RTNs at a time. It wont return the results if more than 2 search values. below is my code:
Private Sub CommandButton1_Click()
Dim Sheet1sh As Worksheet
Dim findapsh As Worksheet
Dim outputsh As Worksheet
Set Sheet1sh = ThisWorkbook.Sheets("Sheet1")
Set findapsh = ThisWorkbook.Sheets("findap")
Set outputsh = ThisWorkbook.Sheets("output")

outputsh.UsedRange.Clear
Sheet1sh.AutoFilterMode = False
'-----
Dim LastRow As Long
Dim Rng As Range, Cell As Range

'finds last row with data in column A
LastRow = findapsh.Cells(findapsh.Rows.Count, 1).End(xlUp).row

'sets the range in column A
Set Rng = Range("A2:A" & LastRow)

For Each Cell In Rng
Cell.value = "*" & Cell.value & "*"
Next Cell
'-----
Dim rtnlist() As String
Dim n As Integer
'this part below, im trying to fix as it only allows me to search max of two RTNs on column A of the "findap" sheet.
'it only put the 1st and 2nd RTN value in the "output" sheet.
'it ignores the 3rd, 4th...RTNs that need to be searched.

n = Application.WorksheetFunction.CountA(findapsh.Range("A:A")) - 2
ReDim rtnlist As String
Dim i As Integer
For i = 0 To n
rtnlist(i) = findapsh.Range("A" & i + 2)
Next i
Sheet1sh.UsedRange.AutoFilter 6, rtnlist(), xlFilterValues
Sheet1sh.UsedRange.Copy outputsh.Range("A1")
Sheet1sh.AutoFilterMode = False
MsgBox ("Search result copied to output sheet.")
End Sub

 

[Astronaut01]

At the RHS, that green table are all your searchvalues.
The green range is a defined nama "Wanted"
The column H is that auxiliary column and is the 8th column of that table, that's the 8 in the VBA-code, you have to change that one to your situation.

If the formula in column H doesn't work, perhaps it's a matrixformula, in my 2021-version, i can't check that. So enter that with CTRL+SHIFT+Enter instead of a normal enter

Sub Filteren()

     Set c = Sheets("dest").Range("a1")                         'destionation topleft
     c.Resize(1000, 10).ClearContents                           'clear

     With Sheets("mysheet").Range("$A$1").CurrentRegion
          .AutoFilter 8, 1
          .SpecialCells(xlVisible).Copy c
          .AutoFilter
     End With

End Sub

Map1
	A	B	C	D	E	F	G	H	I	J	K
1	a	b	c	d	e	f	g	auxiliary			Wanted
2	A2	B2	C2	D2	E2	RTN0002	G2	0			RTN0006
3	A3	B3	C3	D3	E3	RTN0003	G3	0			RTN0009
4	A4	B4	C4	D4	E4	RTN0004	G4	0			RTN00012
5	A5	B5	C5	D5	E5	RTN0005	G5	0			RTN00015
6	A6	B6	C6	D6	E6	RTN0006	G6	1			RTN00018
7	A7	B7	C7	D7	E7	RTN0007	G7	0			RTN00021
8	A8	B8	C8	D8	E8	RTN0008	G8	0			RTN00024
9	A9	B9	C9	D9	E9	xxxxxxxxxxxxxxx RTN0009xxxxxxxxxxxRTN0001	G9	1			RTN00027
10	A10	B10	C10	D10	E10	RTN00010	G10	0			RTN00030
11	A11	B11	C11	D11	E11	RTN00011	G11	0
12	A12	B12	C12	D12	E12	RTN00012	G12	1
13	A13	B13	C13	D13	E13	RTN00013	G13	0
14	A14	B14	C14	D14	E14	RTN00014	G14	0
15	A15	B15	C15	D15	E15	RTN00015	G15	1
16	A16	B16	C16	D16	E16	RTN00016	G16	0
17	A17	B17	C17	D17	E17	RTN00017	G17	0
18	A18	B18	C18	D18	E18	RTN00018	G18	1
19	A19	B19	C19	D19	E19	RTN00019	G19	0
20	A20	B20	C20	D20	E20		G20	0
21	A21	B21	C21	D21	E21	RTN00021	G21	1
22	A22	B22	C22	D22	E22	RTN00022	G22	0
23	A23	B23	C23	D23	E23	RTN00023	G23	0
24	A24	B24	C24	D24	E24	RTN00024	G24	1
25	A25	B25	C25	D25	E25	RTN00025	G25	0
26	A26	B26	C26	D26	E26	RTN00026	G26	0
27	A27	B27	C27	D27	E27	RTN00027	G27	1
28	A28	B28	C28	D28	E28	RTN00028	G28	0
29	A29	B29	C29	D29	E29	RTN00029	G29	0
MySheet

Cell Formulas
Range	Formula
A2:E29,G2:G29	A2	=CHAR(COLUMN()+64)&ROW()
H2:H29	H2	=MAX(--ISNUMBER(SEARCH(Wanted,F2)))

Named Ranges
Name	Refers To	Cells
Wanted	=TBL_Wanted[Wanted]	H2:H29

Hi Bsalv
Any update on my latest screenshot?
Tia