Hiding / Unhiding rows when multiple cell values change

**waverider** · 12-08-2006, 07:35 PM

For the record, I did complete a search on the forums for this issue, but was unable to find a good match for the solution I need.

Following some great programming skills and advice from VBA Noob, I now have a summary page that provides a listing of data based on array formulas (a cool rather long array formula in which each cell contains the following functions: IF, SMALL, ISERROR, INDEX, ROW and COLUMNS!).

I would now like to take the results on this summary page (which are generated from the array formula) and hide any row within a select range (e.g., C10:F:50) where the value in each of those four columns for a single row all equal "" (no result). In other words, if C14, D14, E14 and F14 all equaled "", then the row would be hidden.

I would like this to work automatically and also want it to unhide the rows automatically. For example, if I changed the criteria such that now D14 displayed the value, the row would unhide and show the result. If not possible, then I'd defer to setting up macro button(s) for the same push (manual) result.

Looking forward to any response! Let me know if you need more details.

Thanks!

**davesexcel** · 12-09-2006, 08:35 AM

Here's a code from another forum(MrExcel)

Sub HideRows()
Application.ScreenUpdating = False
Dim i As Integer
Dim RStart As Range
Dim REnd As Range
Set RStart = Range("A2")
Set REnd = Sheets("Sheet1").Range("A65536").End(xlUp).Offset(0, 3)
Range(RStart, REnd).Select
On Error Resume Next
With Selection
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.CountBlank(.Rows(i)) = 4 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
Set RStart = Nothing
Set REnd = Nothing
Range("A1").Select
Application.ScreenUpdating = True
End Sub

found it here
http://www.mrexcel.com/archive/VBA/8771.html

Hiding / Unhiding rows when multiple cell values change

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Hiding / Unhiding rows when multiple cell values change

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions