Hide Column if all cells in this column is empty except first cell

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 hide column (D) if cells is empty starting from (D5 to the end of column)?
alwkeel2002Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

alwkeel2002Author Commented:
i attached my excel sheet with code, but the code will hide column based on determine cell and it's working, 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"  "Attached1"
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

Open in new window


i did the following code but it didn't work and and error is appearing "Attached2"
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

Open in new window

Attached1.xlsm
Attached2.xlsm
0
Ryan ChongCommented:
you can try customize:
Private Sub CommandButton1_Click()
     Call test
End Sub

Sub test()
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To LastCol
        lastRow = Cells(Rows.Count, i).End(xlUp).Row
        Columns(i).EntireColumn.Hidden = lastRow = 1
    Next
End Sub

Open in new window

0
Ryan ChongCommented:
if you only want to do for Column D, you can try this instead:
Sub test2()
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
    Columns("D").EntireColumn.Hidden = lastRow = 1
End Sub

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Or simply this for column D.
Private Sub CommandButton1_Click()
Dim lr As Long
lr = Cells(Rows.Count, "D").End(xlUp).Row

'This button for hide unused column
If lr < 5 Then
    Columns("D").EntireColumn.Hidden = True
Else
    Columns("D").EntireColumn.Hidden = False
End If
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
alwkeel2002Author Commented:
Thanks for your kind help :)
i did the following code for 2 columns, and its working good
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

Open in new window

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

Open in new window

0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.