Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Oct 2015
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Hide column if all cells are empty

    Hello ,
    I am trying to create a macro that will hide columns when all cells is empty starting from determined row.
    for example:
    i have a table with multi columns, and all column has a label then all cells contains numbers, how can i make it filter determined columns to hide them if it's cells is empty but starting from specified raw for end of column

    i attached my excel sheet with code, but the code will hide column based on determine cell and it's working as follow "Attached1":
    Code:
    Private Sub CommandButton1_Click()
    'This button for hide unused column
    If Range("D5").Value = 0 Then
            Columns("D").EntireColumn.Hidden = True
        Else
            Columns("D").EntireColumn.Hidden = False
        End If
    End Sub
    it's hide column "D" if "D5" is empty, but i want ti hide column "D" if it's cells is empty starting from "D5 : to end of column D"
    i did the following code but it didn't work and and error is appearing "Attached2Attached1.xlsm":
    Code:
    Private Sub CommandButton1_Click()
    'This button for hide unused column
    If Range("D5:D1000").Value = 0 Then
            Columns("D").EntireColumn.Hidden = True
        Else
            Columns("D").EntireColumn.Hidden = False
        End If
    End Sub
    Attached Files Attached Files
    Last edited by alwkeel2002; 2015-10-12 at 02:16.

  2. #2
    New Lounger
    Join Date
    Oct 2015
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    any help please?

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Try:
    Code:
    Private Sub CommandButton1_Click()
    Dim LastRow As Long, DataRows As Long
    With ThisWorkbook.Worksheets("DC ")
      With .UsedRange
        LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
        DataRows = .Range("D5:D" & LastRow).SpecialCells(xlCellTypeBlanks).Count
      End With
      If DataRows = LastRow - 4 Then
        .Columns("D").EntireColumn.Hidden = True
      Else
        .Columns("D").EntireColumn.Hidden = False
      End If
    End With
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    New Lounger
    Join Date
    Oct 2015
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your kind reply,
    i try it and it's working, so i made it close column (E) too

    Code:
    Private Sub CommandButton1_Click()
    Dim LastRow As Long, DataRows As Long
    With ThisWorkbook.Worksheets("DC ")
      With .UsedRange
        LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
        DataRows = .Range("D5:D" & LastRow).SpecialCells(xlCellTypeBlanks).Count
      End With
      If DataRows = LastRow - 4 Then
        .Columns("D").EntireColumn.Hidden = True
        .Columns("E").EntireColumn.Hidden = True
    Else
        .Columns("D").EntireColumn.Hidden = False
        .Columns("E").EntireColumn.Hidden = True
      End If
    End With
    End Sub
    I need more one column and i modified your code as follow:
    Code:
    Private Sub CommandButton1_Click()
    Dim LastRow As Long, DataRows As Long
    With ThisWorkbook.Worksheets("DC ")
      With .UsedRange
        LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
        DataRows = .Range("D5:D" & LastRow).SpecialCells(xlCellTypeBlanks).Count
        DataRows = .Range("F5:F" & LastRow).SpecialCells(xlCellTypeBlanks).Count
      End With
      If DataRows = LastRow - 4 Then
        .Columns("D").EntireColumn.Hidden = True
        .Columns("E").EntireColumn.Hidden = True
        .Columns("F").EntireColumn.Hidden = True
        .Columns("G").EntireColumn.Hidden = True
    Else
        .Columns("D").EntireColumn.Hidden = False
        .Columns("E").EntireColumn.Hidden = False
        .Columns("F").EntireColumn.Hidden = False
        .Columns("G").EntireColumn.Hidden = False
      End If
    End With
    End Sub
    and it's working good
    Thank you so much for your kind help

  5. #5
    New Lounger
    Join Date
    Oct 2015
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The code which i modified is hide columns (D & F) and it hide column (F) even it's cells contain values, but i need it to hide only the columns with an empty cells when i press the command button.

  6. #6
    New Lounger
    Join Date
    Oct 2015
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i modified the code and it's working,
    it's hide (D & F) if their cells has no values
    it's hide (D or F) if any of them has no values

    Code:
    Private Sub CommandButton1_Click()
    Dim LastRow As Long, DataRows As Long
    With ThisWorkbook.Worksheets("DC ")
      With .UsedRange
        LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
        DataRows = .Range("D5:D" & LastRow).SpecialCells(xlCellTypeBlanks).Count
      End With
      If DataRows = LastRow - 4 Then
        .Columns("D").EntireColumn.Hidden = True
        .Columns("E").EntireColumn.Hidden = True
      Else
        .Columns("D").EntireColumn.Hidden = False
        .Columns("E").EntireColumn.Hidden = False
      End If
    End With
    
    With ThisWorkbook.Worksheets("DC ")
      With .UsedRange
        LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
        DataRows = .Range("F5:F" & LastRow).SpecialCells(xlCellTypeBlanks).Count
      End With
      If DataRows = LastRow - 4 Then
        .Columns("F").EntireColumn.Hidden = True
        .Columns("G").EntireColumn.Hidden = True
    Else
        .Columns("F").EntireColumn.Hidden = False
        .Columns("G").EntireColumn.Hidden = False
      End If
    End With
    
    End Sub

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    awlkeel,

    You can replace this:
    Code:
      If DataRows = LastRow - 4 Then
        .Columns("D").EntireColumn.Hidden = True
        .Columns("E").EntireColumn.Hidden = True
      Else
        .Columns("D").EntireColumn.Hidden = False
        .Columns("E").EntireColumn.Hidden = False
      End If
    With this:
    Code:
      Columns("D:E").EntireColumn.Hidden = IIf(DataRows = LastRow - 4, "True", "False")
    In both places just changing the column letters.

    BTW: I tested with this code:
    Code:
    Sub Test()
    
      Columns("F:G").EntireColumn.Hidden = IIf([A1] = "", "True", "False")
      
    End Sub
    So I hope I transposed your test into it correctly, still suffering from Jet Lag!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    You could reduce your code to:
    Code:
    Private Sub CommandButton1_Click()
    Dim LastRow As Long, DataRows As Long
    With ThisWorkbook.Worksheets("DC ")
      With .UsedRange
        LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
        DataRows = .Range("D5:D" & LastRow).SpecialCells(xlCellTypeBlanks).Count
      End With
      If DataRows = LastRow - 4 Then
        .Columns("D:E").EntireColumn.Hidden = True
      Else
        .Columns("D:E").EntireColumn.Hidden = False
      End If
      DataRows = .Range("F5:F" & LastRow).SpecialCells(xlCellTypeBlanks).Count
      If DataRows = LastRow - 4 Then
        .Columns("F:G").EntireColumn.Hidden = True
      Else
        .Columns("F:G").EntireColumn.Hidden = False
      End If
    End With
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,720
    Thanks
    146
    Thanked 156 Times in 149 Posts
    Quote Originally Posted by RetiredGeek View Post

    So I hope I transposed your test into it correctly, still suffering from Jet Lag!

    HTH
    Hope you had a great time! How was the museum?

  10. #10
    New Lounger
    Join Date
    Oct 2015
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RetiredGeek:
    Macropod:
    access-mdb:

    Thank you for kind Advice & Reply
    Last edited by alwkeel2002; 2015-10-12 at 15:09.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •