code to hide columns that meet criteria 1

[Allilue]

Hi! Hope someone can help me here...

I want to be able to hide columns within a table if a certain row range is blank. So for example:

Row 1 Description A B C
Row 2 Test 1 X X
Row 3 Test 2 X
Row 4 Test 3 X

So in this case, I want to hide Column B (no marked X's) but leave everything else intact (i.e., Row 1 and Description). Is this something that's easy to do???

Thanks!
Allison

 

[Allilue]

Sorry, is it obvious I'm talking about Excel??? ;o)

 

[xlbo]

How would this routine be triggered? Would it be a manual process (to push a button) or would it need to be dynamic based on the addition / removal of values?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JA3395]

You could use the Cells.Find functionality. If the rows returned is 1 then you know you haven't got any data in that row.

Public Function RetrieveLastRow(ByVal WKName As String) As Long

Dim LastRow
  
On Error Resume Next

    With ActiveWorkbook.Sheets(WKName)
    
[COLOR=green]        '---------------------------------
        ' Find the last real row
        '---------------------------------[/color]
         LastRow = .Cells.Find(What:="*", _
                               SearchDirection:=xlPrevious, _
                               SearchOrder:=xlByRows).row
    
    End With
    
    RetrieveLastRow = LastRow

End Function

as for hiding the column

Private Const cMAX_COL_BLOCKS = 9        [COLOR=green]' A-Z to HA-HZ  (increase this number if more required)[/color]
Private Const cALPHABET = 26

Public Sub HideColIfZero(ByVal Sheet As Worksheet, _
                         ByVal StartColumn As Variant, _
                         Optional ByVal EndColumn As Variant = -1)

Dim lStartCol As Long
Dim lEndCol As Long
Dim i As Long
Dim j As Long
Dim dVal As Double
Dim blnExclude As Boolean

[COLOR=green]    '-------------------------------------------------
    ' Column can be a letter or a number - convert to a number
    '-------------------------------------------------[/color]
    If IsNumeric(StartColumn) = False Then
        lStartCol = ColumnLetterToNumber(StartColumn)
    Else
        lStartCol = StartColumn
    End If

    If IsNumeric(EndColumn) = False Then
        lEndCol = ColumnLetterToNumber(EndColumn)
    Else
        If EndColumn = -1 Then
            lEndCol = lStartCol
        Else
            lEndCol = EndColumn
        End If
    End If

[COLOR=green]    '-------------------------------------------------
    ' process the columns
    '-------------------------------------------------[/color]
    For i = lStartCol To lEndCol
        For j = lStartCol To lEndCol
            dVal = dVal + Abs(Cells(i, j).Value)
        Next j
        
        If (dVal = ZERO_DOUBLE) Then
            blnExclude = False
            For j = LBound(Exclude) To UBound(Exclude)
                If i = Exclude(j) Then
                    blnExclude = True
                    Exit For
                End If
            Next j
            If blnExclude = False Then
                Sheet.Columns(i).EntireColumn.Hidden = True
            End If
        End If
        
        dVal = 0
    Next i

End Sub

Public Function ColumnLetterToNumber(ByVal Letter As String) As Long

Const cA = 64                    [COLOR=green]' Asc("A")[/color]

Dim lTemp As Long
Dim strLetter As String
Dim strTemp As String
Dim i As Long

On Error GoTo ErrorHandler

    strLetter = UCase(Letter)
    
    If Len(strLetter) = 1 Then
        lTemp = Asc(strLetter) - cA
    Else
        For i = 1 To cMAX_COL_BLOCKS
            strTemp = Chr(i + cA)
            If StrComp(strTemp, Left(strLetter, 1), vbTextCompare) = 0 Then
                lTemp = (i * cALPHABET) + Asc(Right(strLetter, 1)) - cA
                Exit For
            End If
        Next i
    End If
    ColumnLetterToNumber = lTemp
    
    Exit Function

ErrorHandler:
    Err.Raise Err.Number, Err.Description
    Err.Clear
End Function

J.

 

[xlbo]

or a bit shorter:

Dim i As Integer

Const a = 3 'Amend as per your 1st column to check
Const b = 23 'Amend as per your 2nd column to check

For i = a To b
  With ActiveSheet.Cells(2, i)
  If .End(xlDown).Row = Columns(i).Cells.Count And .Text = "" Then
    Columns(i).Hidden = True
  End If
  End With
Next i

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Or a bit more automatic

Dim rCol as range

Const a = 3 'Amend as per your 1st column to check

for each rCol in range(cells(1,a), cells(1,a).end(xltoright))
  With ActiveSheet.Cells(2, rcol.column)
  If .End(xlDown).Row = Columns(rcol.column).Cells.Count And .Text = "" Then
    Columns(rcol.column).Hidden = True
  Else
    Columns(rcol.column).Hidden = False
  End If
  End With
Next

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Allilue]

Hi, to answer your question, Geoff, this would be done with a button. So once the user has ticked all relevant X's, the button would trigger the hiding of columns.

I'm also going through the response from you JA3395... Sorry, it's a bit over my head but I'll try to see if I can get this to work. )

But thanks! If there is any further guidance, that's welcome too!!

Allison

 

[xlbo]

In which case may I suggest looking at either myself or Skip's suggestions which should be a little easier to understand

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Allilue]

Hi Again,

Ok, i like the short version better! Now if I need to dummy-proof this completely, can I also add the following details?

In column F, I've created an additional column, so if the value =0, then this column can be hidden once the button is clicked.
Also, the table is covered from F7 to A101.

 

[xlbo]

The table depth does not matter unless you have other info under the table...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Allilue]

Great, thanks! I managed to get this working, however the columns that are hidden are the ones where .Text= "".

This is fine, however I just want to add that the columns to hide should be where there is no visible text. I have formulas in most cells with IF statements, so although there is something in the cell, I'd like to hide only the ones with something visible based on the formula result.

Other than that, I think I'm good!
Thanks again!

 

[combo]

The range (cell) has three properties that can be interesting:
.Value ' what excel assumes for calculations,
.Text ' what the user can see,
.Formula '(with some variants) the contents of edit box, as string.

combo

 

[xlbo]

Please be careful how you phrase your questions - you started off by saying that the criteria is that the column is blank - now you are saying that they are not blank, they just have a "" result from a formula - that is a very different thing

Dim i As Integer, lRow As Long

Const a = 3 'Amend as per your 1st column to check
Const b = 8 'Amend as per your 2nd column to check

For i = a To b

lRow = Cells(65536, i).End(xlUp).Row

  If WorksheetFunction.CountIf(Range(Cells(2, i), Cells(lRow, i)), "X") = 0 Then
      Columns(i).Hidden = True
  End If
 
Next i

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Allilue]

i'm going to be a real pain (sorry!) but i've got a problem i know i should get from the previous responses, but can't seem to... :-(

i get geoff's point about me not explaining things clearly enough about whether cells are blank or result in "".

From the above, i've managed to come up with:
Sub Button93_Click()

Dim i As Integer
Const a = 6 'Amend as per your 1st column to check
Const b = 52 'Amend as per your 2nd column to check

For i = a To b
With ActiveSheet.Cells(6, i)
If .End(xlDown).Row = Columns(i).Cells.Count And .Text = "" Then
Columns(i).Hidden = True
End If
End With
Next i

End Sub

This works for the most part but it's not hiding some cells that i want hidden. I want to hide columns that are either blank or have a result of "". Everything else should remain visible. From the above code, the columns that are not being hidden (that should be) are the ones that have a result of "".

I've tried various suggestions and mixtures of a few, and cannot seem to get this to work in full.

Again, sorry to be a pain, but this would help me a lot !

THANKS!

 

[PHV]

What about this ?
If .End(xlDown).Row = Columns(i).Cells.Count And Trim(.Text) = "" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Allilue]

Sorry, I get the same result as the example i gave...