VBA code to hide empty columns.

Hi.

I need to create VBA code to hide empty columns. Problem is when column considered empty it always has header row(s). I guess code should have defined Range of rows and columns such as A2:AZ50. In this case row is header. Thanks for your help.

Hi
If you have only the one header row then you could account for this eg with:

Sub Hide_Columns()
Dim col As Range

For Each col In ActiveSheet.UsedRange.Columns
    If Application.CountA(col) <= 1 Then col.Hidden = True
Next

End Sub

Hi,
Somehow, it does not work for on row header.
Thanks.

The code as posted will hide any columns which contains either only a single item of data or no items of data (so it assumes that each column has a header value which would therefore count as a single item of data). If this is not working as you would want it to, then you need to explain what is happening that you don't want to happen and also what is not happening that you want to happen.

Perhaps attach an example of your worksheet and explain which columns should be hidden and why?

Hi,
You are right. Somehow it did not work with excel worksheet exported from Access. When I created brand new worksheet it works fine. Sorry and thank you so much for your help.

Hi,
One thing still is confusing me. Excel workbook that I want to apply VBA macro is exported from Access. Column that looks like blank is not hidden by this macro. But when I highlight the same column, delete values (even it looks like no values and column is blank) and run macro again, it works. Can you please help understand what walues could be there and why they exported from Access. Thanks

Do you have any spaces in the column that could otherwise look like blank cells?

No spaces.

Humour me and see if this code works any better:

Sub Hide_Columns()
Dim col As Range, rFound As Range

For Each col In ActiveSheet.UsedRange.Columns
    Set rFound = col.Find(What:="?*",SearchDirection:=xlPrevious)
    If Not rFound Is Nothing Then
       If rFound.Row = 1 Then col.Hidden = True
    Else
       col.Hidden = True
    End If
    Set rFound = Nothing
Next

End Sub

Hi,
I was going to upload my worksheet but this macro works absolutely perfect. Thank you so much.