CODEWITHSUNDEEP
excelvbahideformula
Cristian Radulescu
Cristian Radulescu

Reputation: 3

How do i hide columns with formula output 0 without changing the formula to value

I'm new to this and I am trying to unhide all columns then hide the columns that contain on one row the number "0", the problem is that number is a result of a formula.

This is what I tried:

Sub main()
Rows("1:1").EntireColumn.Hidden = False
Dim Col As Range
For Each Col In Columns("E:EX")
    For Each Row In Rows("9:9")
        Col.Hidden = Col.Find("0", , xlFormulas, xlWhole, , , False) Is Nothing
    Next
Next
End Sub

The result of the code above is that all those columns(E:Ex) get hidden.

Upvotes: 0

Views: 103

Answers (1)

Samuel Hulla
Samuel Hulla

Reputation: 7089

You're on the right track, kinda but few details / improvements to be made

Private Sub hide_zeroes()

  Dim ws as Worksheet: Set ws = Sheets("Your sheet name") '<- edit me
  Dim lc as Long: lc = ws.Cells(9, Columns.Count).End(xlToLeft).Column 'last active Column
  Dim i as Long

  For i = 1 to lc 'from first till last colummn
     If ws.Cells(9, i) = "0" Then 'if 9th row of Column [i] is 0 Then
         ws.Columns(i).EntireColumn.Hidden = True 'hide entire column
     End If
  Next i

End Sub

  1. It's good practice to end your For loops with the variable you're looping. Helps to keep the code readable and makes you avoid unnecessary errors in nested loops

    For i = 1 to 99
   'some code
Next i 'note the i <-

  2. You don't need to check whether it's a formula, just check if the value inside the cell

  3. It's good practice to declare variable names for every variable you're using, or better yet, use Option Explicit (first line of your code) to enable something akin to a "strict mode"

Upvotes: 1

Related Questions