Hide Columns

  • Thread starter Thread starter Abdul Shakeel
  • Start date Start date
A

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
 
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
 
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
 
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
 
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.
 
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
 
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?
 
Think about another plan.

You cannot hide part of a column.


Gord Dibben MS Excel MVP
 
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.
 
Back
Top