Hide rows & columns based on range totals?

[Fabulist]

Hello everyone,

Excuse my ignorance but I found this macro, which works for hidding columns:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("D7).Value = 0 Then
Columns("D").EntireColumn.Hidden = True
Else
Columns("D").EntireColumn.Hidden = False
End If
If Range("H7).Value = 0 Then
Columns("H").EntireColumn.Hidden = True
Else
Columns("H").EntireColumn.Hidden = False
End If

End Sub

But when I try to specify a range "D7:D58" I get a run-time error - D7 to D58 are all numerical values, and if all of them total 0, the column should be hidden. The same I want to apply for a range of of cell values on rows, like A58:K58, which if total 0, the specified row should also be hidden. The latter I have no idea how to do, but I found this:

Sub HideRows()
BeginRow = 1
EndRow = 100
ChkCol = 3

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End Sub

Which I doubt I can properly adjust but I will try to. Currently, combining them gives me a different error. The two I will then combine with a macro related to Deferred Taxes calculation, it works (not well written, extremely long), but I fail to understand how to add a secondary macro to it.

What am I doing wrong? And is it possible to combine at least the two?

Thank you in advance.

 

[WarPigl3t]

myRange = "D7:D58"
If Application.WorksheetFunction.Sum(Range(myRange)) = 0 Then
     Columns(myRange).Hidden = True
Else
     Columns(myRange).Hidden = False
End If

This might not work because I'm telling it to hide the myRange rather than just the column D.

 

[Fabulist]

It does not seem to work or I am doing something wrong, but thank you regardless.

 

[WarPigl3t]

Sub myMacro()
     myRange = "D7:D58"
     c = columnLetterToNumber(myRange)
     If Application.WorksheetFunction.Sum(Range(myRange)) = 0 Then
          Columns(c).Hidden = True
     Else
          Columns(c).Hidden = False
     End If
End Sub

Function columnLetterToNumber(myRange)
     columnLetter = Left(myRange, 1)
     columnLetterToNumber = Columns(columnLetter).Column
End Function

 

[Fabulist]

Amazing, this seems to work very well! I keep repeating this:
myRange = "D7:D58"
c = columnLetterToNumber(myRange)
If Application.WorksheetFunction.Sum(Range(myRange)) = 0 Then
Columns(c).Hidden = True
Else
Columns(c).Hidden = False
End If

myRange = "H7:H58"
c = columnLetterToNumber(myRange)
If Application.WorksheetFunction.Sum(Range(myRange)) = 0 Then
Columns(c).Hidden = True
Else
Columns(c).Hidden = False
End If</pre>
So I can add the columns I need. Do you have any suggestions regarding the rows issue? Can what you wrote be modified and combined for rows as well or I need to try something else separately?

Thank you again.

 

[WarPigl3t]

I don't understand the question

 

[Fabulist]

Well I am trying to do the same for rows based on ranges, e.g. if D85 to J85 returns a total of 0, the entire column should be hidden. Can something like this be made similar to your column macro? Can I combine the two?

 

[WarPigl3t]

If D85 through J85 equals 0 then which column should be hidden? Or did you mean if D85 through J85 equals 0 then row 85 should be hidden?

 

[WarPigl3t]

Sub myMacro()
     firstRow = 1
     lastRow = Range("A" & Rows.Count).End(xlUp).Row
     firstColumn = 4
     lastColumn = 10
     r = firstRow
     Do Until r > lastRow
          c = firstColumn
          Do Until c > lastColumn
               myValue = Cells(r, c).Value
               If myValue <> 0 Then
                    Rows(r).Hidden = True
                    Exit Do
               Else
                    Rows(r).Hidden = False
               End If
               c = c + 1
          Loop
          r = r + 1
     Loop
End Sub

 

[WarPigl3t]

You can combine as many macros as you want. Just make one macro and call as many macros as you want like this.

Sub myMainMacro()
     Call myMacro1()
     Call myMacro2()
End Sub