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.

 

[Fabulist]

I run your second macro and it hides all rows with numbers! Is there a way that I define ranges so it hides rows that total to 0?

And yes, if D85 to J85 equals 0, row 85 should be hidden, let me show you how the table looks like: Imgur: The most awesome images on the Internet

There are more data after the totals, so I need to be able to define ranges and adjust it to other sheets (with completely different rows / columns / data). For example, in this specific sheet which is already running your first macro, I would need 14 to 26 rows automatically hidden. But if row 16 (Sales) had even a single value (e.g. 5,000.00 under Machinery), I would need it to stay visible.

Thank you again.

 

[WarPigl3t]

That's weird. try changing the 0 to "0"

 

[Fabulist]

That's weird. try changing the 0 to "0"

Same, only rows with zero totals appear, the ones with values disappear.

 

[WarPigl3t]

my bad. Change True to False and False to True on the other line of code.

 

[Fabulist]

That is great! Thank you for your time!

I suppose your row related macro will work at any other sheet, automatically hidding rows with 0 totals?

By the way, I did not use "Call" to call both macros, instead I did this (as a final result, so that the macro is automated):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     myRange = "C7:C58"
     c = columnLetterToNumber(myRange)
     If Application.WorksheetFunction.Sum(Range(myRange)) = 0 Then
          Columns(c).Hidden = True
     Else
          Columns(c).Hidden = False
     End If
     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 = "E7:E58"
     c = columnLetterToNumber(myRange)
     If Application.WorksheetFunction.Sum(Range(myRange)) = 0 Then
          Columns(c).Hidden = True
     Else
          Columns(c).Hidden = False
     End If
     myRange = "F7:F58"
     c = columnLetterToNumber(myRange)
     If Application.WorksheetFunction.Sum(Range(myRange)) = 0 Then
          Columns(c).Hidden = True
     Else
          Columns(c).Hidden = False
     End If
     myRange = "G7:G58"
     c = columnLetterToNumber(myRange)
     If Application.WorksheetFunction.Sum(Range(myRange)) = 0 Then
          Columns(c).Hidden = True
     Else
          Columns(c).Hidden = False
     End If
     myRange = "G7:G58"
     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
     myRange = "I7:I58"
     c = columnLetterToNumber(myRange)
     If Application.WorksheetFunction.Sum(Range(myRange)) = 0 Then
          Columns(c).Hidden = True
     Else
          Columns(c).Hidden = False
     End If
     myRange = "J7:J58"
     c = columnLetterToNumber(myRange)
     If Application.WorksheetFunction.Sum(Range(myRange)) = 0 Then
          Columns(c).Hidden = True
     Else
          Columns(c).Hidden = False
     End If
     myRange = "K7:K58"
     c = columnLetterToNumber(myRange)
     If Application.WorksheetFunction.Sum(Range(myRange)) = 0 Then
          Columns(c).Hidden = True
     Else
          Columns(c).Hidden = False
     End If
     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 = False
                    Exit Do
               Else
                    Rows(r).Hidden = True
               End If
               c = c + 1
          Loop
          r = r + 1
     Loop
End Sub


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

This seems to work, although I feel like I am doing something wrong, since it takes x10 times more to run them like this instead of individually.

 

[WarPigl3t]

That is a whole lot of repetitive code. Though this modification will not speed up the process, it does look better.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     Call hideColumnMacro("C7:C58")
     Call hideColumnMacro("D7:D58")
     Call hideColumnMacro("E7:E58")
     Call hideColumnMacro("F7:F58")
     Call hideColumnMacro("G7:G58")
     Call hideColumnMacro("H7:H58")
     Call hideColumnMacro("I7:I58")
     Call hideColumnMacro("J7:J58")
     Call hideColumnMacro("K7:K58")
     Call hideRowMacro()
End Sub

Sub hideColumnMacro(myRange)
     c = columnLetterToNumber(myRange)
     If Application.WorksheetFunction.Sum(Range(myRange)) = 0 Then
          Columns(c).Hidden = True
     Else
          Columns(c).Hidden = False
     End If
End Sub

Sub hideRowMacro()
     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 = False
                    Exit Do
               Else
                    Rows(r).Hidden = True
               End If
               c = c + 1
          Loop
          r = r + 1
     Loop
End Sub

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

 

[Fabulist]

Oh nice, thank you for this, the fix was simple enough but I could not figure it out.

Thank you for everything WarPigl3t!