macro doesn't hide columns for an unexplicable reason
 

Thank you Bernie for the hidecolumn macro. It is easier to read, there is
still a problem however, it doesn't hide any columns.

I know this macro should work but it doesn't. I checked the worksheet
properties, read-only or hidden is not checked. I checked options and there
is nothing under security. I even unfiltered the columns thinking it
wouldn't hide the filtered ones. Why isn't it hiding the columns with n/a.
Could it be it doesn't like the slash and I have to escape it when it
searches for the string n/a ?

Public Sub FORMAT_VOD_HideColumns()
Dim C As Range
Application.ScreenUpdating = False

For Each C In Intersect(Range("12:64000"), ActiveSheet.UsedRange).Columns
If Application.CountIf(C.Cells, "N/A") = C.Cells.Count Then
C.EntireColumn.Hidden = True
Else
C.EntireColumn.Hidden = False

End If
Next C

Application.ScreenUpdating = True

End Sub

What am I missing?

tia,

macro doesn't hide columns for an unexplicable reason
 

Have you tried setting a break point where you think the If statement should
be true to see if it is, in fact, true? Of have you tried stepping through
to see if it is executing the statements as expected?

If it is not hiding the columns, then the first thing to check is if the
criteria is being returned in the If statement.
"Janis" wrote:

Thank you Bernie for the hidecolumn macro. It is easier to read, there is
still a problem however, it doesn't hide any columns.

I know this macro should work but it doesn't. I checked the worksheet
properties, read-only or hidden is not checked. I checked options and there
is nothing under security. I even unfiltered the columns thinking it
wouldn't hide the filtered ones. Why isn't it hiding the columns with n/a.
Could it be it doesn't like the slash and I have to escape it when it
searches for the string n/a ?

Public Sub FORMAT_VOD_HideColumns()
Dim C As Range
Application.ScreenUpdating = False

For Each C In Intersect(Range("12:64000"), ActiveSheet.UsedRange).Columns
If Application.CountIf(C.Cells, "N/A") = C.Cells.Count Then
C.EntireColumn.Hidden = True
Else
C.EntireColumn.Hidden = False

End If
Next C

Application.ScreenUpdating = True

End Sub

What am I missing?

tia,

macro doesn't hide columns for an unexplicable reason
 

I posted this before but never saw it on the reader, so here it is again.

The problem was in your Range("12:64000"). It left a lot of blank cells
beneath your used range. So I set the last row at the bottom of column D in
the UsedRange and it worked. You might want to use a different column to set
the last row, but now you know where the problem lies. Here is the modified
code.

Public Sub FORMAT_VOD_HideColumns() 'Hides a column if all cells in
range = specific value.
Dim C As Range
lr = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
Application.ScreenUpdating = False
For Each C In Intersect(Range("12:" & lr), ActiveSheet.UsedRange).Columns
If Application.CountIf(C.Cells, "N/A") = C.Cells.Count Then
C.EntireColumn.Hidden = True
Else
C.EntireColumn.Hidden = False
MsgBox C.Address
End If
Next C
Application.ScreenUpdating = True
End Sub


"Janis" wrote:

Thank you Bernie for the hidecolumn macro. It is easier to read, there is
still a problem however, it doesn't hide any columns.

I know this macro should work but it doesn't. I checked the worksheet
properties, read-only or hidden is not checked. I checked options and there
is nothing under security. I even unfiltered the columns thinking it
wouldn't hide the filtered ones. Why isn't it hiding the columns with n/a.
Could it be it doesn't like the slash and I have to escape it when it
searches for the string n/a ?

Public Sub FORMAT_VOD_HideColumns()
Dim C As Range
Application.ScreenUpdating = False

For Each C In Intersect(Range("12:64000"), ActiveSheet.UsedRange).Columns
If Application.CountIf(C.Cells, "N/A") = C.Cells.Count Then
C.EntireColumn.Hidden = True
Else
C.EntireColumn.Hidden = False

End If
Next C

Application.ScreenUpdating = True

End Sub

What am I missing?

tia,