need an OR in the countif statement
 

This version does work better. Now it is counting on the columns and I can
see it in the msgbox. Before I think it wasn't counting any columns. It
still doesn't hide any columns though. The reason is there are blank's in
the data rows because there are subtotal rows on groups and those cells don't
have an n/a, so I need to know how to use the OR in the countif or else I
need to not use the countif. I tried
Application.CountIf(C.Cells, "n/A" or " ")
Application.CountIf(C.Cells, "n/a") OR Application.CountIf(C.Cells, " ")
(this last one was really scary, it caused all the n/a cells to be blank
instead of hiding them

tia,
----------------------------------------
Public Sub FORMAT_VOD_HideColumns()
Dim C As Range
Dim lr As Integer


lr = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
Application.ScreenUpdating = False

For Each C In Intersect(Range("12:" & lr), ActiveSheet.UsedRange).Columns
Application.ScreenUpdating = False

If Application.CountIf(C.Cells, "n/A") = C.Cells.Count Then
C.EntireColumn.Hidden = True
MsgBox C.Address
Else
C.EntireColumn.Hidden = False

End If

Next C


Application.ScreenUpdating = True

End Sub

need an OR in the countif statement
 

I haven't tested this but it should work:

If Application.CountIf(C.Cells, "n/a") + Application.CountIf(C.Cells, "
") _
= C.Cells.Count Then


"Janis" wrote:

This version does work better. Now it is counting on the columns and I can
see it in the msgbox. Before I think it wasn't counting any columns. It
still doesn't hide any columns though. The reason is there are blank's in
the data rows because there are subtotal rows on groups and those cells don't
have an n/a, so I need to know how to use the OR in the countif or else I
need to not use the countif. I tried
Application.CountIf(C.Cells, "n/A" or " ")
Application.CountIf(C.Cells, "n/a") OR Application.CountIf(C.Cells, " ")
(this last one was really scary, it caused all the n/a cells to be blank
instead of hiding them

tia,
----------------------------------------
Public Sub FORMAT_VOD_HideColumns()
Dim C As Range
Dim lr As Integer


lr = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
Application.ScreenUpdating = False

For Each C In Intersect(Range("12:" & lr), ActiveSheet.UsedRange).Columns
Application.ScreenUpdating = False

If Application.CountIf(C.Cells, "n/A") = C.Cells.Count Then
C.EntireColumn.Hidden = True
MsgBox C.Address
Else
C.EntireColumn.Hidden = False

End If

Next C


Application.ScreenUpdating = True

End Sub

need an OR in the countif statement
 

One other thing. Your "N/A" will be case sensitive so if you are mixing them
up on the sheet, then you will need to make them all the same case.

If Application.CountIf(C.Cells, LCase("N/A")

"Janis" wrote:

This version does work better. Now it is counting on the columns and I can
see it in the msgbox. Before I think it wasn't counting any columns. It
still doesn't hide any columns though. The reason is there are blank's in
the data rows because there are subtotal rows on groups and those cells don't
have an n/a, so I need to know how to use the OR in the countif or else I
need to not use the countif. I tried
Application.CountIf(C.Cells, "n/A" or " ")
Application.CountIf(C.Cells, "n/a") OR Application.CountIf(C.Cells, " ")
(this last one was really scary, it caused all the n/a cells to be blank
instead of hiding them

tia,
----------------------------------------
Public Sub FORMAT_VOD_HideColumns()
Dim C As Range
Dim lr As Integer


lr = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
Application.ScreenUpdating = False

For Each C In Intersect(Range("12:" & lr), ActiveSheet.UsedRange).Columns
Application.ScreenUpdating = False

If Application.CountIf(C.Cells, "n/A") = C.Cells.Count Then
C.EntireColumn.Hidden = True
MsgBox C.Address
Else
C.EntireColumn.Hidden = False

End If

Next C


Application.ScreenUpdating = True

End Sub

need an OR in the countif statement
 

Disregard the line with the LCase in it. I thought I deleted that.

"JLGWhiz" wrote:

One other thing. Your "N/A" will be case sensitive so if you are mixing them
up on the sheet, then you will need to make them all the same case.

If Application.CountIf(C.Cells, LCase("N/A")

"Janis" wrote:

This version does work better. Now it is counting on the columns and I can
see it in the msgbox. Before I think it wasn't counting any columns. It
still doesn't hide any columns though. The reason is there are blank's in
the data rows because there are subtotal rows on groups and those cells don't
have an n/a, so I need to know how to use the OR in the countif or else I
need to not use the countif. I tried
Application.CountIf(C.Cells, "n/A" or " ")
Application.CountIf(C.Cells, "n/a") OR Application.CountIf(C.Cells, " ")
(this last one was really scary, it caused all the n/a cells to be blank
instead of hiding them

tia,
----------------------------------------
Public Sub FORMAT_VOD_HideColumns()
Dim C As Range
Dim lr As Integer


lr = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
Application.ScreenUpdating = False

For Each C In Intersect(Range("12:" & lr), ActiveSheet.UsedRange).Columns
Application.ScreenUpdating = False

If Application.CountIf(C.Cells, "n/A") = C.Cells.Count Then
C.EntireColumn.Hidden = True
MsgBox C.Address
Else
C.EntireColumn.Hidden = False

End If

Next C


Application.ScreenUpdating = True

End Sub

need an OR in the countif statement
 

I also just noticed that you are using a space between you quote marks for
null.
Don't do that. Use "" not " ", because the one with the space puts readable
data in the cell and you will not get the results you want.

"Janis" wrote:

This version does work better. Now it is counting on the columns and I can
see it in the msgbox. Before I think it wasn't counting any columns. It
still doesn't hide any columns though. The reason is there are blank's in
the data rows because there are subtotal rows on groups and those cells don't
have an n/a, so I need to know how to use the OR in the countif or else I
need to not use the countif. I tried
Application.CountIf(C.Cells, "n/A" or " ")
Application.CountIf(C.Cells, "n/a") OR Application.CountIf(C.Cells, " ")
(this last one was really scary, it caused all the n/a cells to be blank
instead of hiding them

tia,
----------------------------------------
Public Sub FORMAT_VOD_HideColumns()
Dim C As Range
Dim lr As Integer


lr = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
Application.ScreenUpdating = False

For Each C In Intersect(Range("12:" & lr), ActiveSheet.UsedRange).Columns
Application.ScreenUpdating = False

If Application.CountIf(C.Cells, "n/A") = C.Cells.Count Then
C.EntireColumn.Hidden = True
MsgBox C.Address
Else
C.EntireColumn.Hidden = False

End If

Next C


Application.ScreenUpdating = True

End Sub

need an OR in the countif statement
 

I was wrong, it is not case sensitive for CountIf.

"Janis" wrote:

This version does work better. Now it is counting on the columns and I can
see it in the msgbox. Before I think it wasn't counting any columns. It
still doesn't hide any columns though. The reason is there are blank's in
the data rows because there are subtotal rows on groups and those cells don't
have an n/a, so I need to know how to use the OR in the countif or else I
need to not use the countif. I tried
Application.CountIf(C.Cells, "n/A" or " ")
Application.CountIf(C.Cells, "n/a") OR Application.CountIf(C.Cells, " ")
(this last one was really scary, it caused all the n/a cells to be blank
instead of hiding them

tia,
----------------------------------------
Public Sub FORMAT_VOD_HideColumns()
Dim C As Range
Dim lr As Integer


lr = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
Application.ScreenUpdating = False

For Each C In Intersect(Range("12:" & lr), ActiveSheet.UsedRange).Columns
Application.ScreenUpdating = False

If Application.CountIf(C.Cells, "n/A") = C.Cells.Count Then
C.EntireColumn.Hidden = True
MsgBox C.Address
Else
C.EntireColumn.Hidden = False

End If

Next C


Application.ScreenUpdating = True

End Sub

need an OR in the countif statement
 

So considering that there might also be cells that contain formulas but no
values we should include "0" in the equation. Here is what the last version
looks like.

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") + _
Application.CountIf(C.Cells, "") + Application.CountIf(C.Cells, "0") _
= C.Cells.Count Then
C.EntireColumn.Hidden = True
Else
C.EntireColumn.Hidden = False
End If
Next C
Application.ScreenUpdating = True
End Sub

So any combination of "N/A", null, or zero should add up to the cell count
in the range and if it don't, then you have other data in that column and you
don't want to hide it.

"Janis" wrote:

This version does work better. Now it is counting on the columns and I can
see it in the msgbox. Before I think it wasn't counting any columns. It
still doesn't hide any columns though. The reason is there are blank's in
the data rows because there are subtotal rows on groups and those cells don't
have an n/a, so I need to know how to use the OR in the countif or else I
need to not use the countif. I tried
Application.CountIf(C.Cells, "n/A" or " ")
Application.CountIf(C.Cells, "n/a") OR Application.CountIf(C.Cells, " ")
(this last one was really scary, it caused all the n/a cells to be blank
instead of hiding them

tia,
----------------------------------------
Public Sub FORMAT_VOD_HideColumns()
Dim C As Range
Dim lr As Integer


lr = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
Application.ScreenUpdating = False

For Each C In Intersect(Range("12:" & lr), ActiveSheet.UsedRange).Columns
Application.ScreenUpdating = False

If Application.CountIf(C.Cells, "n/A") = C.Cells.Count Then
C.EntireColumn.Hidden = True
MsgBox C.Address
Else
C.EntireColumn.Hidden = False

End If

Next C


Application.ScreenUpdating = True

End Sub