Hide Macro if Found

The Animal

Active Member
Joined
May 26, 2011
Messages
449
Hi
I would like to create a little sort button using a macro rather than a filter so all rows hide for rows 5:100 if the text Awages and Atotals are found in A5:A100

Any help would be great.
Thanks Stephen
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,834
Sorting doesn't hide rows. To hide rows you could use 1) Autofilter or 2) manually checking every cell against those values.
 

The Animal

Active Member
Joined
May 26, 2011
Messages
449
Sorry meant Autofilter
Would like to have a macro that I can add to a button to do the job instead?
 
Last edited:

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
Try:
Code:
Sub HideRows()

    Dim rng     As Range
    Dim rngHide As Range
    
    Const str As String = "AwagesAtotals"

    Cells.EntireRow.Hidden = False

    For Each rng In Range("A5:A100")
        If InStr(str, rng.Value) > 0 Then
            If Not rngHide Is Nothing Then
                Set rngHide = Union(rng, rngHide)
            Else
                Set rngHide = rng
            End If
        End If
    Next rng
    
    If Not rngHide Is Nothing Then
        rngHide.EntireRow.Hidden = True
        Set rngHide = Nothing
    End If
        
End Sub
 
Last edited:

frank_AL

Active Member
Joined
Oct 30, 2015
Messages
439
Jack Dan Ice beat me to it but here is another approach
Code:
Sub Find_Text_Strings()
    Dim FindString1 As String, FindString2 As String
    Dim String1 As Boolean, String2 As Boolean
    Dim Rng As Range
    FindString1 = "Awages"
    FindString2 = "ATotal"
    String1 = False
    String2 = False


'   Check for Awages as FindString1
        With Sheets("Sheet1").Range("A5:A100")
            Set Rng = .Find(What:=FindString1, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                String1 = True
            End If
        End With
        
 '  Check for Atotal as String2
        With Sheets("Sheet1").Range("A5:A100")
            Set Rng = .Find(What:=FindString2, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                String2 = True
            End If
        End With
        If String1 = True Then
            If String2 = True Then
                Rows("5:100").EntireRow.Hidden = True
            Else
                MsgBox "Both text items Awages and Atotal were not found"
            End If
        Else
            MsgBox "Both text items Awages and Atotal were not found"
        End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,862
Members
414,409
Latest member
FloordAlex

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
Top