Hide Columns Based on Value in a certain Row

[05Li139]

Hello All,

I'm trying to hide all columns in a range based on a zero value in an =SUM cell at the top of the column.

I've used the following macro to hide rows based on a zero value;

Sub Hide_ZeroYearsLife()
Dim LastRow As Long, c As Range
Application.EnableEvents = False
LastRow = Cells(Cells.Rows.Count, "K").End(xlUp).Row
On Error Resume Next
For Each c In Range("K12:K" & LastRow)
If c.Value = 0 Then
c.EntireRow.Hidden = True
ElseIf c.Value = 1 Then
c.EntireRow.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub


And edited it to the following to hide the columns:


Sub Hide_ZeroCountShortfall()
Dim LastColumn As Long, c As Range
Application.EnableEvents = False
LastColumn = Cells(Cells.Columns.Count, "Row10").End(xlUp).Column
On Error Resume Next
For Each c In Range("V10:DE10" & LastColumn)
If c.Value = 0 Then
c.EntireColumn.Hidden = True
ElseIf c.Value = 1 Then
c.EntireColumn.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub


However I keep getting a debug message based on the red highlighted row


Can anyone advise

Thank you

 

[steve the fish]

You would write that like this:

LastColumn = Cells(10, Cells.Columns.Count).End(xlToLeft).Column

The cells method requires rows first then columns. So this will produce the last cell in row 10.