Hide empty columns on active row(s)

[Berenloper]

Hi everyone,

I'm trying to hide empty columns on the active row (and preferably on multiple rows), but can't find the right code for it.
I have this:

Sub Hide_Columns_No_Value()
  
Dim c As Range
  
    For Each c In Range("A1:GU1").Cells
        If c.Value = Not IsEmpty(ActiveCell.Value) Then
            c.EntireColumn.Hidden = True
        End If
    Next c
End Sub

So, when I'm on row 3 e.g. I wan't the code to run on that row.
It would be even better if selecting more than 1 row also hides all empty columns within that selection!

Thanks for helping me out on this.

Regards,

Berenloper

 

[Jimmypop]

Hi there

Hope this helps...

To hide empty columns on the active row (and multiple rows), you can use the following VBA code:

Sub Hide_Columns_No_Value()
  
    Dim c As Range
    Dim selectedRows As Range
    Dim lastColumn As Long
    
    Set selectedRows = Selection.Rows
    lastColumn = selectedRows.Columns(selectedRows.Columns.Count).Column
    
    For Each c In Range("A" & selectedRows.Row & ":G" & selectedRows.Row + selectedRows.Count - 1).Cells
        If WorksheetFunction.CountA(Range(Cells(c.Row, lastColumn), Cells(c.Row, c.Column))) = 0 Then
            c.EntireColumn.Hidden = True
        Else
            c.EntireColumn.Hidden = False
        End If
    Next c
    
End Sub

This code will first determine the last column of the selected rows, then loop through all cells in the selected rows' range. For each cell, it will use the CountA worksheet function to count the number of non-empty cells in the range from the current cell to the last column of the selected rows. If the count is 0, the entire column will be hidden. Otherwise, the column will be unhidden (in case it was previously hidden).

To use this code, select the rows you want to hide empty columns for, then run the Hide_Columns_No_Value macro.

 

[Berenloper]

Hi Jimmypop,

Thanks for helping!
I should have been more specific, because it's not working as I hoped.

Take this as an example:

	A	B	C	D	E	F
Row1		X	X	X
Row2	X		X			X

Selecting Row1 should hide columns A, E and F
Selecting Row2 should hide columns B, D and E
Selecting both rows should hide column E

Regards,

Berenloper

 

[kevin9999]

Maybe this?
EDITED

Option Explicit
Sub Berenloper()
    Dim r As Range, LCol As Long, i As Long
    LCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
    Set r = Range(Selection.Cells(1, 1), Cells(1, LCol))
    
    If WorksheetFunction.CountA(r) = 0 Then
        MsgBox "Only blank rows selected - exiting sub"
        Exit Sub
    End If
    
    If r.Rows.Count = 1 Then
        For i = LCol To 1 Step -1
            If r.Cells(1, i) = "" Then r.Columns(i).EntireColumn.Hidden = True
        Next i
    Else
        For i = LCol To 1 Step -1
            If WorksheetFunction.CountA(r.Columns(i)) = 0 Then r.Columns(i).EntireColumn.Hidden = True
        Next i
    End If
End Sub

 

[kevin9999]

This is better.

Option Explicit
Sub Berenloper2()
    Dim LCol As Long, i As Long
    Cells.EntireColumn.Hidden = False
    LCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
    If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
        MsgBox "Only blank rows selected - exiting sub"
        Exit Sub
    End If
    
    If Selection.Rows.Count = 1 Then
        For i = LCol To 1 Step -1
            If Selection.Cells(1, i) = "" Then
                Selection.Columns(i).EntireColumn.Hidden = True
            Else
                Selection.Columns(i).EntireColumn.Hidden = False
            End If
        Next i
    Else
        For i = LCol To 1 Step -1
            If WorksheetFunction.CountA(Selection.Columns(i)) = 0 Then
                Selection.Columns(i).EntireColumn.Hidden = True
            Else
                Selection.Columns(i).EntireColumn.Hidden = False
            End If
        Next i
    End If
End Sub

 

[Berenloper]

Hello Kevin9999,

Yes! Works brilliantly
Exactly what I was looking for. Thanks!!

Regards,

Berenloper

 

[kevin9999]

Hello Kevin9999,

Yes! Works brilliantly
Exactly what I was looking for. Thanks!!

Regards,

Berenloper

Glad it worked out and thanks for the feedback

 

[Berenloper]

Hello everyone,

I'm still happy with the given solution, but would like a small extension.
When hiding the empty columns, is it possible to also hide the unselected row(s), except for rows 1 and 2? These two must therefore always remain visible.

Thanks in advance for your response.

Regards,

Berenloper

 

[kevin9999]

I think I understand what you want. You do realise of course that you'll need to manually unhide the hidden rows before you change your selection (if you wanted to include those hidden rows in any subsequent selection that is). Try this on a copy of your worksheet:

Option Explicit
Sub Berenloper3()
    Dim LCol As Long, LRow As Long, i As Long
    Cells.EntireColumn.Hidden = False
    LCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
    LRow = Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
        MsgBox "Only blank rows selected - exiting sub"
        Exit Sub
    End If
    
    'Hide the columns first
    If Selection.Rows.Count = 1 Then
        For i = LCol To 1 Step -1
            If Selection.Cells(1, i) = "" Then
                Selection.Columns(i).EntireColumn.Hidden = True
            Else
                Selection.Columns(i).EntireColumn.Hidden = False
            End If
        Next i
    Else
        For i = LCol To 1 Step -1
            If WorksheetFunction.CountA(Selection.Columns(i)) = 0 Then
                Selection.Columns(i).EntireColumn.Hidden = True
            Else
                Selection.Columns(i).EntireColumn.Hidden = False
            End If
        Next i
    End If
    
    'Hide the rows next
    For i = LRow To 3 Step -1
        If Intersect(Rows(i), Selection.Rows) Is Nothing Then
            Rows(i).EntireRow.Hidden = True
        Else
            Rows(i).EntireRow.Hidden = False
        End If
    Next i
End Sub

 

[Berenloper]

Hello kevin9999,

Yes, that works! I'm very happy with it
I did notice (also in "Berenloper2", but not a real problem), that it doesn't work perfect on non-contiguous rows. So, selecting row 3 and row 5 for example would only show correct results for row 3. It would be nice if that also worked, but as said before, not a real problem for me. Till now I always select one or more rows among each other.

Thanks for your effort!

Regards,

Berenloper