Hide rows with no value in visible columns

[JBM91]

Hi experts,

So, much like the title of the thread suggest, I have been trying to string together a code that will allow me to hide rows if said rows does not have any cell values in the visible columns of my worksheet.
The idea is that in my dataset I might be interested in only seeing values for a specific week - and as such, all other columns (weeks) will be hidden. More to that end, I also only want to see rows where a value is present within my specified range.

The first piece of the puzzle - namely that of hiding columns based on a specified value - I've managed to solve with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim cel As Range, Headers As Range
  Dim s As String
 
  Set Headers = Range("G1:ABC1")
  If Target.Address = "$E$16" Then
    s = Target.Value
    Application.ScreenUpdating = False
    If s = "" Then
      Headers.EntireColumn.Hidden = False
    Else
      For Each cel In Headers
        cel.EntireColumn.Hidden = Not cel.Value = s
      Next cel
    End If
    Application.ScreenUpdating = True
  End If

 
End Sub

So far so good!

However, the second part - namely to then further only showcase rows with cell value within the specified range of the code posted above - I simply can't figure out.

At first I looked into the subtotal function of Excel, but that apparently only works on filtered values and hidden rows, not columns.
I then tried to create a volatile public function to count whether any values were present in my specified range to use as a helper - it worked, but it took ages to calculate every time a change was added to the data range. So long in fact that it just isn't an option.

I'm relatively new to VBA and coding in general, so any tips, tricks and suggestions would be greatly appreciated!

Best regards,

Jannick

 

[Trevor_S]

Does this do what you want?

For CheckRows = 2 to 10 'Range of row numbers to check
  VisibleValues = ""
    For CheckCols = 2 to 25 'Range of columns numbers (not letters) to check, if not hidden
      If ActiveSheet.Columns(CheckCols).Hidden = False Then VisibleValues = VisibleValues & Cells(CheckRows, CheckCols).Value
    Next
  If VisibleValues = "" Then
    ActiveSheet.Rows(CheckRow).EntireRow.Hidden = True
  Else
    ActiveSheet.Rows(CheckRow).EntireRow.Hidden = False
  End If
Next

It works it's way through the data line by line, concatenating the values of every visible column. If the concatenated value is blank, the row will be hidden, otherwise it will be visible.

I've noticed that your original macro is set to run on Worksheet Change. Particularly if you add my code to it, it could be very slow! Could you have the macro triggered by an "update" button instead?

 

[Rick Rothstein]

A question for clarification...

When you say blank cells, are you including cells with formulas that are displaying the empty text string ("")?

 

[Trevor_S]

Good point! As it stands, my code hides rows where the cell value is blank - regardless of whether the cell itself is empty, or contains a formula that results in an empty text string.

If you only want hide rows where the cells are empty, at the end of the fourth row replace .Value with .Formula

This will then concatenate the formulae themselves, rather than their results, and only hide the line if the concatenation of the formulae is an empty text string.

 

[JBM91]

A question for clarification...

When you say blank cells, are you including cells with formulas that are displaying the empty text string ("")?

All cells are populated manually, so there are no formulas present within the range itself to result in a ""-text string!

For CheckRows = 2 to 10 'Range of row numbers to check VisibleValues = "" For CheckCols = 2 to 25 'Range of columns numbers (not letters) to check, if not hidden If ActiveSheet.Columns(CheckCols).Hidden = False Then VisibleValues = VisibleValues & Cells(CheckRows, CheckCols).Value Next If VisibleValues = "" Then ActiveSheet.Rows(CheckRow).EntireRow.Hidden = True Else ActiveSheet.Rows(CheckRow).EntireRow.Hidden = False End If Next

I tried adding the code you suggested to the code I posted previously and it worked! Unfortunately, much like you already predicted, it takes a very long time for it to calculate the threads of the various row. I also tried keeping it separate in a sub function, and then assigned it to a button instead like you suggested, but the long processing time remains (which I guess makes sense, since the code is the same it's just been moved to a different "trigger"-option)

 

[Trevor_S]

Yes, the processing time will be the same, I just thought that you might need to run it less often - a bit like how you can have large spreadsheets set to manual recalculation if needed.

You could try adding Application.ScreenUpdating = False before the code, and Application.ScreenUpdating = True after, but I'm not sure how much that would speed it up.

 

[JBM91]

Yes, the processing time will be the same, I just thought that you might need to run it less often - a bit like how you can have large spreadsheets set to manual recalculation if needed.

You could try adding Application.ScreenUpdating = False before the code, and Application.ScreenUpdating = True after, but I'm not sure how much that would speed it up.

I tried that as well - unfortunately it doesn't seem to mitigate the issue.

It's perfectly fine though! My objective was to find a formula that could hide blank cells within my visible data range - and the code provided by you does exactly that - I'll just have to work around the processing time, which is quite frankly just a "luxury-issue" more than a functional problem.

Your time, help and input is MUCH appreciated, Trevor - thanks!