How can you hide a row on Excel using a formula based on condition?

Answer 1

There is no native Excel formula which allows you to conditionally hide an entire row based on user-defined criteria. You can however create a simple macro which would leverage the VBA command “EntireRow.Hidden” to accomplish this.

For example, let’s assume you have a worksheet with daily sales figures in Column B (column number 2) for a sole proprietorship brick and mortar retail business for the last 12 months and there’s one header row followed by 365 rows of data representing each day of the last year (for a total of 366 rows). However, while this single employee business is generally open 7 days a week, it is closed on various holidays and when the owner is on vacation. The sales data is directly exported from the business Point of Sale system which does not account for whether the business is open on any given day.  Instead of manually hiding the rows of data corresponding to when the business is closed, you want Excel to automatically perform this task. You can insert an ‘Active X’ command button into the worksheet which would launch the following macro I have written for you at the click of a button. When executed, the macro examines each of the 365 rows of sales data in column B of the worksheet and then hides any row where the sales for that day is less than $.01. When the macro has completed its tasks, a dialog box appears detailing how many rows have been hidden.

Sub HideRow()

   Dim Hidecount As Integer

   StartRow = 2

   EndRow = 366

   ColumnNum = 2

   

Hidecount = 0

   For RowCnt = StartRow To EndRow

       If Cells(RowCnt, ColumnNum).Value < .01 Then

       Hidecount = Hidecount + 1

           Cells(RowCnt, ColumnNum).EntireRow.Hidden = True

       Else

           Cells(RowCnt, ColumnNum).EntireRow.Hidden = False

       End If

   Next RowCnt

   MsgBox Hidecount & " rows have been sucessfully hidden"

End Sub