Previous Topic Next Topic

Hide Blank Columns

Closed
Jack - Feb 15, 2011 at 02:49 PM
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 - Feb 16, 2011 at 01:05 AM
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!!!!!!!!!


1 reply

Answer 1 / 1
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 805
Feb 16, 2011 at 01:05 AM
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
1