Microsoft Excel - Search for text, hide the columns

Asked By James Brimble on 15-Feb-09 02:40 PM

Hi there everyone. I have tried various codes to achieve the following but thus far have failed. I am now at the point of requesting a bit of help.. My problem is I want to search a range of cells eg. b2:zz50 to find text in a specific cell eg. a1="Helicopter". I want the code to then hide all columns that do not contain the text "Helicopter" in them. I also want the code to show columns that have cells containing "helicopter" & if they contain other texts. I don't know if I have described this very clearly. I am about pulling out my hair now. Any help is greatly appreciated. Thanks

Consider this

Panji Tengkorak replied to James Brimble on 15-Feb-09 11:46 PM

Sub Button1_Click()
    Dim i As Integer
    For i = 1 To 7 ' the column sequence you want to evaluate
        Call HideColumnIfNoHelicopter(i, 15)
    Next
End Sub

Sub HideColumnIfNoHelicopter(colIndex As Integer, stopRow As Integer)
    Dim myRange As Range
    Dim iCol As Integer
    Set myRange = Range(Cells(1, colIndex), Cells(stopRow, colIndex))
    iCol = 0
    For Each Cell In myRange.Cells
        If LCase(Cell) = "helicopter" Then
           iCol = 0
           Exit For
        Else
           iCol = Cell.Column
        End If
    Next
    If iCol > 0 Then
       Columns(iCol).Hidden = True
    End If
End Sub

Hide Items -- Excel 97/Excel 2000

alice johnson replied to James Brimble on 16-Feb-09 12:42 AM

Sub HidePivotItemsVisible()
'hide all pivot items in all tables on sheet
'except last item
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
   For Each pf In pt.RowFields
    pf.AutoSort xlManual, pf.SourceName
    For Each pi In pf.PivotItems
        pi.Visible = False
    Next
Next
pf.AutoSort xlAscending, pf.SourceName
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Hide Items in Specific Field -- Excel 97/Excel 2000

alice johnson replied to James Brimble on 16-Feb-09 12:43 AM

The following code will prompt you for a field name, and will hide all items in the specified field.

Sub PivtoHideItemsField()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strPF As String
Set pt = ActiveSheet.PivotTables(1)
strPF = InputBox("What Field?", "Field Name")
Set pf = pt.PivotFields(strPF)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
   With pf
    .AutoSort xlManual, .SourceName
     For Each pi In pf.PivotItems
         pi.Visible = False
     Next pi
    .AutoSort xlAscending, .SourceName
    End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

TRY THIS

C_A P replied to James Brimble on 16-Feb-09 05:29 AM

Private Sub Worksheet_Change(ByVal Target As  Range) 
    Dim c As Range 
     
    For Each c In Target 
         
        If c.Column = 13 And InStr(c.Value, "Other (specify in next column)") Then 
            Columns("N").Hidden = False 
        ElseIf WorksheetFunction. CountIf(Columns("M"), "Other (specify in next column)") = 0 Then 
            Columns("N").Hidden = True 
        End If 
         
        If c.Column = 15 And InStr(c.Value, "Other (specify in next column)") Then 
            Columns("P").Hidden = False 
        ElseIf WorksheetFunction.CountIf(Columns("O"), "Other (specify in next column)") = 0 Then 
            Columns("P").Hidden = True 
        End If 
         
    Next c 
     
End Sub

TRY THIS

C_A P replied to James Brimble on 16-Feb-09 05:31 AM

Sub Hide_April_May() 
     
     'ADDED for month number
    Dim mth As Long 
     
    Dim cel As Range 
    Dim rng As Range 
     
     '             Set range here
    Set rng = Range("A10:AD10") 
     
     'ADDED for error
    On Error Goto notdate 
     
    For Each cel In rng 
        If cel <> "" Then 
            If UCase(cel) = "APRIL" Or UCase(cel) = "MAY" Then 
                cel.EntireColumn.Hidden = True 
            End If 
             
             'ADDED to check month number
            mth = Month(cel) 
            Select  Case mth 
            Case 2, 4, 5, 7, 8, 10, 11 
                cel.EntireColumn.Hidden = True 
            End Select 
        End If 
         
         'ADDED for error
notdate: 
         
    Next cel 
     
End Sub

Hide Items in Specific Field -- Excel 97/Excel 2000

James Brimble replied to alice johnson on 20-Feb-09 09:53 AM

Hello Alice,

Thank you very much for your help.

Although I have not used a pivot table before, it looks like this will work very well with what I need to achieve.

What I need is to specify what I want to keep i.e. "Helicopter". I guess it would be very similar not the function of 'Auto Filter'. If it is possible to have multiple criteria that would be brilliant. i.e. only show cells between certain dates..... or I am I dreaming?

I run the code from a macro.

At the moment I can't get the code to run.....do I need to define where/what the table is?

Sub PivtoHideItemsField()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strPF As String
Set pt = ActiveSheet.PivotTables(1)

'the VBA editor throws a message "Can't execute code in break mode" at this point.
strPF = InputBox("What Field?", "Field Name")
Set pf = pt.PivotFields(strPF)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
   With pf
    .AutoSort xlManual, .SourceName
     For Each pi In pf.PivotItems
         pi.Visible = False
     Next pi
    .AutoSort xlAscending, .SourceName
    End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

I hope to assign VBA to a button to run the code.

Thanks for your help in advance.

If it helps I can send you a copy of the sheet in question.