Hide Blank Columns Closed

Question

Hello, 

I am writing a Macro that will hide blank columns, but leave columns with data in them alone.  Here is my macro:

Sub HideColumns()
    BeginCol = 2
    EndCol = 7
    ChkRow = 9

    For ColCnt = BeginCol To EndCol
        If Cells(ColCnt, ChkRow).Value = "" Then
            Cells(ColCnt, ChkRow).EntireColumn.Hidden = True
        Else
            Cells(ColCnt, ChkRow).EntireColumn.Hidden = False
        End If
    Next ColCnt
End Sub

I want to hide any one of columns 2 thru 7 if row 9 in any of those columns are blank.  This macro doesn't do anything.  If I change the ChkRow = 9 to say ChkRow = 12, it will hide column 12 only.  Please HELP!!!!!!!!!


Configuration: Windows XP / Internet Explorer 6.0

venkat1926 · Answer

modified macro is given below
in cells property the first argument is row no. and second argument is column no.
you have reversed

hidden=true is enough.  
hidden =false is not necessary 

It is a good prctise that in the macro design stage you can have message boxes to check whether macro is ding what you want. once macro is tested you can convert the msgbox statements as comment by putting a single apostrophe in the beginning of the statement. 

Sub HideColumns()
Dim begincol As Integer, endcol As Integer, chkrow As Integer
Dim colcnt As Integer
begincol = 2
endcol = 7
chkrow = 9

For colcnt = begincol To endcol
MsgBox colcnt
'If Cells(colcnt, chkrow).Value = "" Then
If Cells(chkrow, colcnt) = "" Then
Cells(chkrow, colcnt).EntireColumn.Hidden = True

'Cells(colcnt, chkrow).EntireColumn.Hidden = True
'Else
'Cells(colcnt, chkrow).EntireColumn.Hidden = False
End If
Next colcnt
End Sub

Hide Blank Columns

1 response

Similar discussions

Subscribe To Our Newsletter!