Filter multiple columns for a single word

[oceanorigami]

I want to run a filter "contains" RND on multiple columns but can't figure out how to do it. I think I need to use a macro or the Advanced Filter. Can anyone help explain how to do it?

 

[lfcreds11]

Hi I just was reading this post and was wondering if you only wanted this for specific columns how would you write that?
Thank you,
Jen

OK, the columns are not really clear to me. From you sample data I have assumed you want to check from column C to the last column in your data and only display rows that have "RND" somewhere in those columns. If that is not what you want, please advise exactly which column letters need to be checked.

To implement ..

1. With your worksheet active, Alt+F11 to open the Visual Basic window

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Your workbook will need to be saved as a macro-enabled file (*.xlsm)

6. From the workbook press Alt+F8 to open the Macro dialog (or View tab -> Macros or Developer tab (if you have it) Macros) -> Macros and select Showq_My_Rows_of_Interset then press 'Run'

Sub Show_My_Rows_of_Interest()
  Dim rCrit As Range
  Dim sFormula As String, sRng As String
  
  Const TextOfInterest As String = "RND"
  Const FirstColOfInterest As String = "C"
  Const fBase As String = "=COUNTIF(#,""*%*"")"
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  With ActiveSheet
    On Error Resume Next
    .ShowAllData
    On Error GoTo 0
    With .UsedRange
      sRng = FirstColOfInterest & "2:" & .Cells(2, .Columns.Count).Address(0, 0)
      Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
      rCrit.Cells(2).Formula = Replace(Replace(fBase, "#", sRng, 1, -1, 1), "%", TextOfInterest, 1, -1, 1)
      .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
    End With
    rCrit.ClearContents
  End With
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  
End Sub

To show all the rows again, either ..

a) Data ribbon tab|Clear (in the 'Sort & Filter' section), or

b) Run the following macro

Sub Show_All_Rows()
  On Error Resume Next
  ActiveSheet.ShowAllData
  On Error GoTo 0
End Sub

 

[Peter_SSs]

.. if you only wanted this for specific columns ..

Wanted what for specific columns?
a) The range to be filtered?
b) The columns to check for the TextOfInterest?
c) Something else?

If b) then what columns? Do these columns have something in their headers that would identify the columns to check & exclude other columns?
Could we have a small set of dummy sample data with an explanation related to that sample?

 

[Kerfirou]

OK, the columns are not really clear to me. From you sample data I have assumed you want to check from column C to the last column in your data and only display rows that have "RND" somewhere in those columns. If that is not what you want, please advise exactly which column letters need to be checked.

To implement ..

1. With your worksheet active, Alt+F11 to open the Visual Basic window

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Your workbook will need to be saved as a macro-enabled file (*.xlsm)

6. From the workbook press Alt+F8 to open the Macro dialog (or View tab -> Macros or Developer tab (if you have it) Macros) -> Macros and select Showq_My_Rows_of_Interset then press 'Run'

Sub Show_My_Rows_of_Interest()
  Dim rCrit As Range
  Dim sFormula As String, sRng As String
 
  Const TextOfInterest As String = "RND"
  Const FirstColOfInterest As String = "C"
  Const fBase As String = "=COUNTIF(#,""*%*"")"
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  With ActiveSheet
    On Error Resume Next
    .ShowAllData
    On Error GoTo 0
    With .UsedRange
      sRng = FirstColOfInterest & "2:" & .Cells(2, .Columns.Count).Address(0, 0)
      Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
      rCrit.Cells(2).Formula = Replace(Replace(fBase, "#", sRng, 1, -1, 1), "%", TextOfInterest, 1, -1, 1)
      .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
    End With
    rCrit.ClearContents
  End With
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
 
End Sub

To show all the rows again, either ..

a) Data ribbon tab|Clear (in the 'Sort & Filter' section), or

b) Run the following macro

Sub Show_All_Rows()
  On Error Resume Next
  ActiveSheet.ShowAllData
  On Error GoTo 0
End Sub

Hello, excuse me for the late reply. I came across this script, and I was wondering what should be change if say, the heading is in row 1-6?

Thank you for any advice you can give.

 

[Peter_SSs]

Hello, excuse me for the late reply. I came across this script, and I was wondering what should be change if say, the heading is in row 1-6?

Welcome to the MrExcel board!

What do you mean by "headings in row 1-6"?
- Six rows of headings?
- Six rows merged into one row?
- Something else?

Since the post you are referring to is 6 years old & therefore well out of the front of my mind, it might be a good idea to set out just what you have and what you are trying to achieve.

Some sample data and expected results might also help clarify. (XL2BB)

 

[Kerfirou]

Welcome to the MrExcel board!

What do you mean by "headings in row 1-6"?
- Six rows of headings?
- Six rows merged into one row?
- Something else?

Since the post you are referring to is 6 years old & therefore well out of the front of my mind, it might be a good idea to set out just what you have and what you are trying to achieve.

Some sample data and expected results might also help clarify. (XL2BB)

I mean six row of heading. My problem is basically the same as OP, where I have several field for many transactions, and I want to know if, say, John involved in any of the transaction as any side of it.

The excel of which I make it might be using several rows as heading for the classification, hence the question about making it so the sorting started in, for example, the seventh row.

 

[Peter_SSs]

started in, for example, the seventh row.

Assuming there is something in row 1, try making these 2 changes

sRng = FirstColOfInterest & "2:" & .Cells(2, .Columns.Count).Address(0, 0)
sRng = FirstColOfInterest & "7:" & .Cells(7, .Columns.Count).Address(0, 0)

.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
.Offset(5).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False

 

[Kerfirou]

Assuming there is something in row 1, try making these 2 changes

sRng = FirstColOfInterest & "2:" & .Cells(2, .Columns.Count).Address(0, 0)
sRng = FirstColOfInterest & "7:" & .Cells(7, .Columns.Count).Address(0, 0)

.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
.Offset(5).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False

Okay, I'll try the code. Thanks for the reply!

 

[SurveyGal123]

@Peter_SSs , thank you so much!! I so needed this info.

 

[tinejf]

OK, the columns are not really clear to me. From your sample data I have assumed you want to check from column C to the last column in your data and only display rows that have "RND" somewhere in those columns. If that is not what you want, please advise exactly which column letters need to be checked.

To implement ..

1. With your worksheet active, Alt+F11 to open the Visual Basic window

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Your workbook will need to be saved as a macro-enabled file (*.xlsm)

6. From the workbook press Alt+F8 to open the Macro dialog (or View tab -> Macros or Developer tab (if you have it) Macros) -> Macros and select Showq_My_Rows_of_Interset then press 'Run'

Sub Show_My_Rows_of_Interest()
  Dim rCrit As Range
  Dim sFormula As String, sRng As String
 
  Const TextOfInterest As String = "RND"
  Const FirstColOfInterest As String = "C"
  Const fBase As String = "=COUNTIF(#,""*%*"")"
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  With ActiveSheet
    On Error Resume Next
    .ShowAllData
    On Error GoTo 0
    With .UsedRange
      sRng = FirstColOfInterest & "2:" & .Cells(2, .Columns.Count).Address(0, 0)
      Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
      rCrit.Cells(2).Formula = Replace(Replace(fBase, "#", sRng, 1, -1, 1), "%", TextOfInterest, 1, -1, 1)
      .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
    End With
    rCrit.ClearContents
  End With
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
 
End Sub

To show all the rows again, either ..

a) Data ribbon tab|Clear (in the 'Sort & Filter' section), or

b) Run the following macro

Sub Show_All_Rows()
  On Error Resume Next
  ActiveSheet.ShowAllData
  On Error GoTo 0
End Sub

Hi @Peter_SSs,
I don't know if this question is coming too late, but I was wondering if you could help me to use this filtering method, but for limited columns.
Meaning, it should filter all columns containing RND from column "F" to column "N".
Would this be possible?

Thank you in advance!

 

[Peter_SSs]

Hi @Peter_SSs,
I don't know if this question is coming too late, but I was wondering if you could help me to use this filtering method, but for limited columns.
Meaning, it should filter all columns containing RND from column "F" to column "N".
Would this be possible?

Thank you in advance!

Welcome to the MrExcel board!

Is this what you mean? If not, I suggest that you start a new thread and detail your exact requirements. You could add a link to this thread if you think it would be useful to your potential helpers.

I have assumed that column Z is available to use as helper column for the Advanced Filter.

Sub Show_My_Rows_of_Interest()
  Dim rCrit As Range
 
  Const TextOfInterest As String = "RND"
  Const ColsOfInterest As String = "F:N"
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  With ActiveSheet
    On Error Resume Next
    .ShowAllData
    On Error GoTo 0
    Set rCrit = .Range("Z1:Z2")
    With Intersect(.UsedRange, .Columns(ColsOfInterest))
      rCrit.Cells(2).Formula = Replace(Replace("=COUNTIF(#,""*%*"")", "#", .Rows(2).Address(0, 0)), "%", TextOfInterest)
      .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
    End With
    rCrit.Cells(2).ClearContents
  End With
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub