Hide Columns

[Abdul Shakeel]

Hi All,



I am searching for macro that hide or delete column(s) if they haven’t any
values, & if any cell in that particular column(s) have any value then we
couldn’t delete or hide the column.



Regards,



Shakeel

 

[Jacob Skaria]

Dear Shakeel

Try the below and feedback...

Sub HideBlankColumns()
Dim lngCol As Long
Dim lngLastRow As Long
Dim lngLastCol As Long
lngLastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row
lngLastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
Range("A1", Cells(1, lngLastCol)).Columns.Hidden = False
For lngCol = 1 To lngLastCol
If WorksheetFunction.CountBlank(Range(Cells(1, lngCol), _
Cells(lngLastRow, lngCol))) = lngLastRow Then
Columns(lngCol).Hidden = True
End If
Next
End Sub

 

[Abdul Shakeel]

Dear Jacob Skaria

your code is almost work for me but I think I should alter my question more,
if I want this condition to be applied on some specific cells (e.g. column A
B C) so what would be the code in this situation

 

[Jacob Skaria]

Dear Shakeel

Instead of

lngLastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column


you can directly mention it as 3 if you want this to run only for A,B,C
(last column 3)

lngLastCol = 3

If this post helps click Yes

 

[Abdul Shakeel]

Dear Jacob Skaria

Sorry for bothering you, if write lngcol = 3 its work perfectly but what if
I want macro to run on column BCD only.

 

[Jacob Skaria]

Change lngCol from 1 to 2 as below

Sub HideBlankColumns()
Dim lngCol As Long
Dim lngLastRow As Long
Dim lngLastCol As Long
lngLastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row
lngLastCol = 4
Range("A1", Cells(1, lngLastCol)).Columns.Hidden = False
For lngCol = 2 To lngLastCol
If WorksheetFunction.CountBlank(Range(Cells(1, lngCol), _
Cells(lngLastRow, lngCol))) = lngLastRow Then
Columns(lngCol).Hidden = True
End If
Next
End Sub

 

[Jodie]

Hi Jacob,

This is something that I would like to use, but I would like it to ignore
the first row. And, I need it for all sheets in the workbook. Are you able
to help me with this?

 

[Gord Dibben]

Think about another plan.

You cannot hide part of a column.


Gord Dibben MS Excel MVP

 

[Jodie]

Thank you Gord, for your input. However, I am not looking to hide part of a
column. What I am referring to is to ignore the first row when determining
if the column is blank or equals zero. If everything is blank or equals zero
after the first row, then hide the entire column.