code to auto hide blank columns

[hocs]

I need help to auto hide columns if the result of a cell (with firmula) returns value (blank or #N/A) other than numbers:
Case:
cell G5 to Z5 will capture and show invoice numbers from a summary sheet if formula criterials are met. (i.e. G5 to Z5 contain formulas), if not, it will be blank (i use IF and ISNA to 'convert' #N/A to blank). Is it possible to auto hide column with 'blank' result?
Please advise!
Thanks!

 

[PennStateBrian]

Try the below, it will hash through each of the cells in the range and hide the columns if the value is blank.

Sub hideColumns()

Dim cell As Range

For Each cell In Sheets("Sheet1").Range("G1:Z1")
If cell.Value = "" Then
cell.EntireColumn.Hidden = True
End If
Next

End Sub

 

[hocs]

Thanks PSBrian.

1. I have to run this code manually. Can i assign a cell whereby when i click on it, this code will run?

2. It hide blank columns but can't unhide even if the next cell (H5) captured new invoice number and so on.

Please help!

Thanks!

 

[Bill Rockenbach]

Copy this code to a standard code module and create a button on the worksheet to run it.
(You code use the Worksheet_BeforeDoubleClick event, but you would have to double click a cell to run the code.)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Option Explicit<o:p></o:p>
 <o:p></o:p>
Sub HideColumns()<o:p></o:p>
    Dim rng As Range<o:p></o:p>
    Dim c As Range<o:p></o:p>
 <o:p></o:p>
    For Each c In Sheets("Sheet1").Range("G1:Z1")<o:p></o:p>
        If Len(c) = 0 Then<o:p></o:p>
            If rng Is Nothing Then<o:p></o:p>
                Set rng = c<o:p></o:p>
            Else<o:p></o:p>
                Set rng = Union(rng, c)<o:p></o:p>
            End If<o:p></o:p>
        End If<o:p></o:p>
    Next c<o:p></o:p>
    rng.EntireColumn.Hidden = True<o:p></o:p>
End Sub

<o></o>
Put this code in the worksheet code module. The code will unhide column G5 to Z5 if H5 is populated with an order number

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)<o:p></o:p>
    If Not IsEmpty(Range("H5")) Then<o:p></o:p>
        Range("G5:Z5").EntireColumn.Hidden = False<o:p></o:p>
    End If<o:p></o:p>
[FONT=Times New Roman]End Sub[/FONT]

 

[hocs]

Thanks Bill.

How to improve the unhide code:
If I5 returns an invoice number, it will unhide G5:I5 (showing 3 inovoice numbers), if J5 returns an invoice number, it will unhide G5:J5 (showing 4 invoice numbers),and so on i.e unhiding from left to right whenever there is new invoice being created, until Z5.

Thanks!

 

[Bill Rockenbach]

Option Explicit
'If I5 returns an invoice number, it will unhide G5:I5 (showing 3 invoice numbers),
'if J5 returns an invoice number, it will unhide G5:J5 (showing 4 invoice numbers),
'and so on until Z5.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsEmpty(Range("H5:Z5")) Then
Range("G5:" & Target.Address).EntireColumn.Hidden = False
End If
End Sub

 

[hocs]

Hi Bill,

Somehow, after hiding, the unhiding part just not working...

 

[Bill Rockenbach]

Try this code in the sheet module to unhide rows

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
For i = 26 To 7 Step -1
If Len(Cells(5, i)) <> 0 Then
Range(Cells(5, 7), Cells(5, i)).EntireColumn.Hidden = False
Exit For
End If
Next i
End Sub