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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

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

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

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.
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.