Hide columns that contain numbers

[seamanm125]

Hi,

I'm trying to create a macro that hides any columns that do not contain any numbers. for example:



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cat[/TD]
[TD]dog[/TD]
[TD]mouse 3[/TD]
[TD]mouse 5[/TD]
[TD]mouse 4[/TD]
[TD]Fred[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Turns to:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]mouse 3[/TD]
[TD]mouse 5[/TD]
[TD]mouse 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]



I realize that when a number is written alongside text, excel doesn't recognize it as a number. That fine, all I need is a wildcard search for all of my column headings and exclude the ones that don't contain "1" , "2", "3" , "4" , "5".

Any help would be greatly appreciated.

Cheers,

Matt

 

[ryansclev]

There may be a better way to do this, and if you have a lot of columns, this way might take a few seconds, but it should work for what you are trying to do.

Sub HideNonNumeric()
Dim intStep As Integer
Dim strColumnName As String
Dim booHide As Boolean
'Assuming Column Names start in A1
Range("A1").Select
'Loop through All Columns in the sheet
Do While ActiveCell.Column <= ActiveSheet.UsedRange.Columns.Count
    strColumnName = ActiveCell
    If strColumnName <> "" Then
        booHide = True
        intStep = 1
        'step through each bit in the string and see if there is a number
        'if there is, set boohide to false to keep the column visible
        For intStep = 1 To Len(strColumnName)
            If IsNumeric(Mid(strColumnName, intStep, 1)) Then
                booHide = False
                Exit For
            End If
        Next
    End If
    
'if there is no number, hide the column
If booHide = True Then
    Selection.EntireColumn.Hidden = True
End If
'Go To Next Column
ActiveCell.Offset(0, 1).Select
Loop
End Sub

 

[Gavin T]

Try this:

Sub HideCols()

    Dim rng As Range

    For Each rng In ActiveSheet.UsedRange.Rows(1).Cells
        If Not IsNumeric(Right(rng.Value, 1)) Then Columns(rng.Column).Hidden = True
    Next rng

End Sub

 

[seamanm125]

Wow these macros are gold class thank you so much. ryansclev I used yours as I didn't want it to affect the first column so I changed Range ("A1") to ("B1") and it worked like a charm. Here is a kid on a skateboard for all your trouble O{-<[:

Can anyone help me with the last challenge. Now that the columns are hidden is there a way to hide the rows that are not displaying an X. This is difficult for me to do as there may be a column that is hidden now and has an X in it making it technically not blank row. I'll use another example

Figure 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cat
[/TD]
[TD]Dog
[/TD]
[TD]Mouse 3
[/TD]
[TD]Mouse 5
[/TD]
[TD]Mouse 4
[/TD]
[TD] Fred
[/TD]
[/TR]
[TR]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Turns to:
Figure 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]mouse 3
[/TD]
[TD]mouse 5
[/TD]
[TD]mouse 4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[/TR]
</tbody>[/TABLE]


What I want:
Figure 3
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Mouse 3
[/TD]
[TD]Mouse 5
[/TD]
[TD]Mouse 4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]x
[/TD]
[/TR]
</tbody>[/TABLE]

figure 2 is showing the spreadsheet where the columns have been filtered using the macro. There is a row in the middle that has no "x" displayed anywhere but there is one hidden under the cat column (see figure 1). Is there a way to filter the rows in figure 2 to hide the rows that display no "x" so we end up with a spread sheet looking like figure 3? Bear in mind that there column A will have my row headings so those will need to be excluded. By doing this I can print off the final result. The columns/ rows that I need to print will change from week to week so by having this problem solved will help so much.

Again, any help will be much appreciated.

Regards,

Matt

 

[ryansclev]

I believe this should work. Just replace Column N in the range value for whatever your final column is.

Sub HideRows()
Dim rng As Range
Dim rngVisible As Range
Dim i As Integer
For i = 1 To ActiveSheet.UsedRange.Rows.Count
    Set rng = Range("B" & i & ":N" & i)
    Set rngVisible = rng.SpecialCells(xlVisible)
    If Application.WorksheetFunction.CountA(rngVisible) = 0 Then
       rngVisible.EntireRow.Hidden = True
    End If
Next
Set rng = Nothing
Set rngVisible = Nothing
End Sub

 

[seamanm125]

Your a champion thanks so much for your help.