ExcelBanter - Hide Columns

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Hide Columns (https://www.excelbanter.com/excel-programming/429148-hide-columns.html)

Hi All,

I am searching for macro that hide or delete column(s) if they havent any
values, & if any cell in that particular column(s) have any value then we
couldnt 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

--
If this post helps click Yes
---------------
Jacob Skaria

"Abdul Shakeel" wrote:

Hi All,

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

Regards,

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
--
Regards,

"Jacob Skaria" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria

"Abdul Shakeel" wrote:

Hi All,

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

Regards,

Shakeel

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
---------------
Jacob Skaria

"Abdul Shakeel" wrote:

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
--
Regards,

"Jacob Skaria" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria

"Abdul Shakeel" wrote:

Hi All,

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

Regards,

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.

--
Regards,

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria

"Abdul Shakeel" wrote:

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
--
Regards,

"Jacob Skaria" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria

"Abdul Shakeel" wrote:

Hi All,

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

Regards,

Shakeel

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

--
If this post helps click Yes
---------------
Jacob Skaria

"Abdul Shakeel" wrote:

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.

--
Regards,

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria

"Abdul Shakeel" wrote:

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
--
Regards,

"Jacob Skaria" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria

"Abdul Shakeel" wrote:

Hi All,

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

Regards,

Shakeel

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?
--
Thank you, Jodie

"Jacob Skaria" wrote:

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

--
If this post helps click Yes
---------------
Jacob Skaria

"Abdul Shakeel" wrote:

Hi All,

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

Regards,

Shakeel

Think about another plan.

You cannot hide part of a column.

Gord Dibben MS Excel MVP

On Fri, 30 Oct 2009 06:34:01 -0700, Jodie
wrote:

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?

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.
--
Thank you, Jodie

"Gord Dibben" wrote:

Think about another plan.

You cannot hide part of a column.

Gord Dibben MS Excel MVP

On Fri, 30 Oct 2009 06:34:01 -0700, Jodie
wrote:

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?

.