Tech Support Guy banner

Solved: Macro to hide column with zeros

934 Views 5 Replies 2 Participants Last post by  CodeLexicon
Hi There

I'm struggling with a very simple macro.

Here is what I want the macro to do.

I have zeros in Column A. The zeros go from A1 to A11. I want the macro to do the following. If range("A1:A11") are all zeros then hide column A.


Not open for further replies.
1 - 6 of 6 Posts
Here you go. There's a module with the macro in.

I've used activesheet but if you want to refer to a specific sheet you can change activesheet to sheets("YOURSHEETNAME")


Hi CodeLexicon

I forgot to mention that I have Excel 2000 and as such cannot opn your Excel file. Can you save it as Excel 2000 please and resend

Thank you so much. I'm in my late 60's and my memory is not all that great.

Ok - here you go:

Option Explicit
Sub AllZeros()
Dim CRange As Range

'defines range as the Activesheet, cells A1:A11
'you can swap the Activesheet reference for Sheets("INSERTYOURSHEETNAME")
Set CRange = ActiveSheet.[a1:a11]
Dim C As Long
Dim Cn As Long

'Cn is a simple counter - it starts at Zero and every time a cell in your Crange (check range) has a zero
'the counter adds 1
Cn = 0

'This is where the macro looks at each cell in Crange to check if it has a 0
For C = 1 To CRange.Rows.Count
If CRange(C).Value = 0 Then
Cn = Cn + 1
End If

'Finally, it checks if the counter (Cn) equals the number of rows in Crange
'if it does, it hides the entire column
If Cn = CRange.Rows.Count Then
CRange(1).EntireColumn.Hidden = True
End If

End Sub


See less See more
Hi CodeLexicon

Thank you so much for your help with this macro. It woks like a charm.

No problem :)
1 - 6 of 6 Posts
Not open for further replies.