none
Hiding Columns with No Data RRS feed

  • Question

  • Hello,

    I have a spreadsheet with a large number of rows and columns.  When I click on a given row, I'd like to only see columns which have a value.  For row 6 that might be Columns A, B, AX, BC.  For row 20 that might be Columns A, F, G, AS, AZ.

    Hiding columns with no data would make much easier to absorb the information.  Please include all the initiation and details as I'm still learning.

    Thanks, John

    Friday, June 7, 2019 9:43 PM

All replies

  • Hi John,

    I assume:
      1) Rows 1 and 2 are header row, or always visible.
      2) max Row is 10,000, and max Column is 256 (IV).
    I've made a sample:

    Option Explicit
    
    ' ---
    Private maxRow As Integer
    Private maxCol As Integer
    Private CurrentRow As Integer
    Private PreviousRow As Integer
    
    ' ---
    Private Sub Worksheet_Activate()
        maxRow = 10000
        maxCol = 256
        PreviousRow = 0
    End Sub
    ' ---
    Private Sub btn_VisibleAll_Click()
        Cells.Select
        Selection.EntireRow.Hidden = False
        Selection.EntireColumn.Hidden = False
        Range("A1").Select
    End Sub
    ' ---
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Application.ScreenUpdating = False
        ' --- get CurrentRow
        CurrentRow = Selection.Row
        Range("A1").Value = CurrentRow
        If (CurrentRow < 3) Then
            Exit Sub    ' -- header row selected, do nothing
        End If
        If (CurrentRow = PreviousRow) Then
            Exit Sub    ' -- same row selected, do nothing
        Else
            PreviousRow = CurrentRow
        End If
        ' --- hidden rows  cf. row 1-2 (header rows) is always visible
        Range("A3:A" & CurrentRow - 1).EntireRow.Hidden = True
        Range("A" & CurrentRow + 1 & ":A" & maxRow).EntireRow.Hidden = True
        ' --- show only columns where values are
        Dim col As Integer
        For col = 1 To maxCol
            If (Cells(CurrentRow, col).Value = "") Then
                Columns(col).Hidden = True
            End If
        Next
        ' ---
        Application.ScreenUpdating = False
    End Sub

    Regards,


    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Sunday, June 9, 2019 2:06 AM
  • Hi Ashidacchi,

    Thanks so much for your help!  Your code will help me hide not only columns, but also rows which haven't be selected.

    However I'm encountering the following error:

         Run-time error '1004':

         Method 'Range' of Object'_Worksheet' failed

    at the following line of code.

         Range("A" & CurrentRow + 1 & ":A" & maxRow).EntireRow.Hidden = True

    When I hover over variable names I'm seeing the following values:

         maxRow = 0
         maxCol  = 0

    This doesn't make sense because their values shouldn't be changing from their initial values of 10000 and 256 respectively.

    Do you have any thoughts on what might be the problem?

    Thanks, John

    Monday, June 10, 2019 6:09 PM
  • Hi John,

    Please change code like this:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Application.ScreenUpdating = False
        ' --- get CurrentRow
        CurrentRow = Selection.Row
        Range("A1").Value = CurrentRow
        If (CurrentRow < 3) Then
            Exit Sub    ' -- header row selected, do nothing
        End If
        If (CurrentRow = PreviousRow) Then
            Exit Sub    ' -- same row selected, do nothing
        Else
            PreviousRow = CurrentRow
        End If
        ' ========= change code like the below =================
        ' --- hidden rows  cf. row 1-2 (header rows) is always visible
        Range("A3:A10000").EntireRow.Hidden = True
        Range("A" & CurrentRow).EntireRow.Hidden = False
        ' ============================================
        ' --- show only columns where values are
        Dim col As Integer
        For col = 1 To maxCol
            If (Cells(CurrentRow, col).Value = "") Then
                Columns(col).Hidden = True
            End If
        Next
        ' ---
        Application.ScreenUpdating = False
    End Sub

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html


    • Edited by Ashidacchi Monday, June 10, 2019 11:39 PM
    Monday, June 10, 2019 11:37 PM
  • It works like a charm.  Thanks for your help!
    • Marked as answer by VBA novice Wednesday, June 12, 2019 7:44 PM
    • Unmarked as answer by VBA novice Wednesday, June 12, 2019 7:46 PM
    Wednesday, June 12, 2019 7:44 PM
  • I'm sorry Ashidacchi; I clicked answered too soon.  The code no longer crashes and does filter out other rows, but it no longer filters out columns without data. Please let me know how to modify from here. Thanks, John
    Wednesday, June 12, 2019 7:49 PM
  • Hi John,

    Could you explain more detail or exact about "it no longer filters out columns without data."?
    How should VBA code work, if a row has no data in its columns?

    And you should mark on my post as an answer instead of yours, if my post is helpful.

    Regards,


    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html


    • Edited by Ashidacchi Wednesday, June 12, 2019 8:22 PM
    Wednesday, June 12, 2019 8:11 PM
  • Hi John,

    Do you want to show a selected row, even if it has no data in its columns?
    If so, code would be like this.
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Application.ScreenUpdating = False
        ' --- get CurrentRow
        CurrentRow = Selection.Row
        Range("A1").Value = CurrentRow
        If (CurrentRow < 3) Then
            Exit Sub    ' -- header row selected, do nothing
        End If
        If (CurrentRow = PreviousRow) Then
            Exit Sub    ' -- same row selected, do nothing
        Else
            PreviousRow = CurrentRow
        End If
        ' --- hidden rows  cf. row 1-2 (header rows) is always visible
        Range("A3:A10000").EntireRow.Hidden = True
        Range("A" & CurrentRow).EntireRow.Hidden = False
        ' --- show only columns where values are
        Dim dataCnt As Integer: dataCnt = 0
        Dim col As Integer
        For col = 1 To maxCol
            If (Cells(CurrentRow, col).Value = "") Then
                Columns(col).Hidden = True
                dataCnt = dataCnt + 1
            End If
        Next
        ' --- Show a selected row, even if it has no data.
        If (dataCnt = 0) Then
            Columns(1).Hidden = False
        End If
        ' ---
        Application.ScreenUpdating = False
    End Sub
    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html


    • Edited by Ashidacchi Saturday, June 15, 2019 1:07 AM
    Saturday, June 15, 2019 1:07 AM
  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Hide rows and columns with no data.
    This is the default mode of PP and PQ.
    No formulas, no VBA macro needed.
    Additional clickable filtering is standard.
    http://www.mediafire.com/file/ykvl1x5lp8c98ra/06_14_19a.xlsx/file
    http://www.mediafire.com/file/9f411p4at9xt2d7/06_14_19a.pdf/file


    Updated so first 3 months of 2018 always show.
    Saturday, June 15, 2019 2:24 AM
  • Hi,<o:p></o:p>

    My spreadsheet is 75 rows and 75 columns (I modified maxRow and maxCol accordingly).<o:p></o:p>

    The first three columns should always show, even if columns 4-75 have no data.  I'd like to hide columns in the range of 4-75 if they do not have any data.

    Thanks for your help!

    John :-)

    p.s.  I tried to include screenshots but received a message that "Body text cannot contain images or links until we are able to verify your account."  I followed the instructions to verify my account to no avail. Do you know if there is a group moderator who can help me with this?

    Tuesday, June 18, 2019 2:57 PM
  • Hi John,

    How about this?
    ' --- This code is in Worksheet [SelectionChange]
    Private maxRow As Integer
    Private maxCol As Integer
    Private PreviousRow As Integer
    Private CurrentRow As Integer
    
    ' ---
    Private Sub Worksheet_Activate()
        maxRow = 75     ' -- change according to your sheet
        maxCol = 75     ' -- change according to your sheet
        PreviousRow = 0 ' -- should not be changed
        ' ---
        Range("A1:A75").EntireRow.Hidden = False
    End Sub
    ' ---
    Private Sub btn_VisibleAll_Click()
        Cells.Select
        Selection.EntireRow.Hidden = False
        Selection.EntireColumn.Hidden = False
        Range("A1").Select
    End Sub
    ' ---
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Application.ScreenUpdating = False
        ' --- get CurrentRow
        CurrentRow = Selection.Row
        Range("A1").Value = CurrentRow
        If (CurrentRow < 3) Then
            Exit Sub    ' -- header row selected, do nothing
        End If
        If (CurrentRow = PreviousRow) Then
            Exit Sub    ' -- same row selected, do nothing
        Else
            PreviousRow = CurrentRow
        End If
        ' --- hidden rows  cf. row 1-2 (header rows) is always visible
        Range("A3:A75").EntireRow.Hidden = True
        Range("A" & CurrentRow).EntireRow.Hidden = False
        ' --- show only columns where values are
        Dim dataCnt As Integer: dataCnt = 0
        Dim col As Integer
        For col = 1 To maxCol
            If (Cells(CurrentRow, col).Value = "") Then
                Columns(col).Hidden = True
                dataCnt = dataCnt + 1
            End If
        Next
        ' --- If current row has no data, column 1(A) - 75(BW) is hidden.
        If (dataCnt = 0) Then
            Dim myRange As String
            myRange = "A" & CurrentRow & ":BW" & CurrentRow
            Range(myRange).EntireColumn.Hidden = True
        End If
        ' ---
        Application.ScreenUpdating = False
    End Sub
    I've shared my sample via OneDrive. Please download and check it.
    https://1drv.ms/x/s!AhzOJeY5F3-fk4hpaP1VJmNx6BwFHQ?e=RYscdj

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Wednesday, June 19, 2019 3:06 AM