VBA Help to Auto hide/unhide rows based on cell value

[miamiman33176]

I would like to auto hide and unhide an entire row based upon the values of one cell in that row for the entire worksheet. I chose the C column to place the formula to determine if the row should be auto hidden or auto unhidden. I can make the formula to display true, false, hide, or unhide. I just am having an issue with the VBA code. Can anyone please assist?

 

[Rick Rothstein]

I would like to auto hide and unhide an entire row based upon the values of one cell in that row for the entire worksheet. I chose the C column to place the formula to determine if the row should be auto hidden or auto unhidden. I can make the formula to display true, false, hide, or unhide. I just am having an issue with the VBA code. Can anyone please assist?

Because the cells you are using as your trigger contain formulas, you cannot use the Change event for those cells because formulas to not evoke Change... you either need to use the Calculate event or have the Change event monitor the all the cells the formula references assuming those cells do not contain formulas (those are what actually change, the ultimate non-formula cells which the main formula is dependent on... when one of those non-formula cells get changed, then all the formulas that ultimately depend on it update to display new values). Can you show us the formula you want to place in Column C?

 

[miamiman33176]

Thanks for your prompt reply. The formula would be:

=IF(A2="","HIDE","UNHIDE")
=IF(A3="","HIDE","UNHIDE")
=IF(A4="","HIDE","UNHIDE")
and so on.

All of these formulas are in the C Column.

 

[Rick Rothstein]

Thanks for your prompt reply. The formula would be:

=IF(A2="","HIDE","UNHIDE")
=IF(A3="","HIDE","UNHIDE")
=IF(A4="","HIDE","UNHIDE")
and so on.

All of these formulas are in the C Column.

In that case, you do not need any formulas in Column C at all (at least not for the purposed of triggering the hiding of rows)... all you need to do is have the Change event monitor the values in Column A. Something like this event code should probably work for you...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 Then
    If Target.Column = 1 Then
      Target.EntireRow.Hidden = Target.Value = ""
    End If
  End If
End Sub

 

[miamiman33176]

What would trigger the hide\unhide in A?

 

[Rick Rothstein]

What would trigger the hide\unhide in A?

I have no idea as I know nothing about your data, its layout or how you intend it to be used. However, with that said, whatever you had planned that would have made your formulas in Column C display TRUE/FALSE (or HIDE/UNHIDE) would make the code I posted work as well since ultimately you are only interested in whether the cell that got changed in Column A was changed to display "" or not... that change could come from the user deleting the value in Column A (which would then hide the row) or from VBA code manipulating the value in the cell..

 

[miamiman33176]

Can you please help with the VBA based on the results of column C? I know that it can be done as I did it with columns and have having a problem doing it automatically with rows.

 

[miamiman33176]

The VBA you provided is not working properly as the contents of column A is changed by another formula and is not manually manipulated which would trigger the hide\unhide.

 

[IFTODE]

Hi,
i need the exact same code. In column A i have a similar formula like Miamiaman33176 , =if(COUNTA(B3:V3)>0;"Unhide";""), i need the rows to be hiden by this formula.