macro doesn't hide columns for an unexplicable reason

G

Guest

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

Guest

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.
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top