Something is wrong with the OR statement on the line with the asterisks. I
want to hide the columns that have all n/a's as the only data cells. There
could be some blanks also but otherwise no data. Also, I was trying to debug
it and see what column it was counting. I can't tell if it works since my OR
statement is wrong.
tia,




For Each C In Intersect(Range("12:64000"), ActiveSheet.UsedRange).Columns
** If Application.CountIf(C.Cells, "n/a" Or " ") = C.Cells.Count Then ***
C.EntireColumn.Hidden = True
C.columnIndex = colIndex
Debug.Print colIndex
Else
C.EntireColumn.Hidden = False

End If

Next C


Application.ScreenUpdating = True

End Sub

Try this:

If Application.CountIf(C.Cells, "n/a") Or Application.CountIf(C.Cells, " ")
= .Cells.Count

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Janis" wrote:

Something is wrong with the OR statement on the line with the asterisks. I
want to hide the columns that have all n/a's as the only data cells. There
could be some blanks also but otherwise no data. Also, I was trying to debug
it and see what column it was counting. I can't tell if it works since my OR
statement is wrong.
tia,




For Each C In Intersect(Range("12:64000"), ActiveSheet.UsedRange).Columns
** If Application.CountIf(C.Cells, "n/a" Or " ") = C.Cells.Count Then ***
C.EntireColumn.Hidden = True
C.columnIndex = colIndex
Debug.Print colIndex
Else
C.EntireColumn.Hidden = False

End If

Next C


Application.ScreenUpdating = True

End Sub

Janis, you will most likely have problems using the Or statement since it
will never equal Cells.Count. I posted a modified code on your other posting
that will work.

"Michael" wrote:

Try this:

If Application.CountIf(C.Cells, "n/a") Or Application.CountIf(C.Cells, " ")
= .Cells.Count

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Janis" wrote:

Something is wrong with the OR statement on the line with the asterisks. I
want to hide the columns that have all n/a's as the only data cells. There
could be some blanks also but otherwise no data. Also, I was trying to debug
it and see what column it was counting. I can't tell if it works since my OR
statement is wrong.
tia,




For Each C In Intersect(Range("12:64000"), ActiveSheet.UsedRange).Columns
** If Application.CountIf(C.Cells, "n/a" Or " ") = C.Cells.Count Then ***
C.EntireColumn.Hidden = True
C.columnIndex = colIndex
Debug.Print colIndex
Else
C.EntireColumn.Hidden = False

End If

Next C


Application.ScreenUpdating = True

End Sub

Oh, here it is Michael gave me the answer below. Thanks for the OR statement.

"Michael" wrote:

Try this:

If Application.CountIf(C.Cells, "n/a") Or Application.CountIf(C.Cells, " ")
= .Cells.Count

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Janis" wrote:

Something is wrong with the OR statement on the line with the asterisks. I
want to hide the columns that have all n/a's as the only data cells. There
could be some blanks also but otherwise no data. Also, I was trying to debug
it and see what column it was counting. I can't tell if it works since my OR
statement is wrong.
tia,




For Each C In Intersect(Range("12:64000"), ActiveSheet.UsedRange).Columns
** If Application.CountIf(C.Cells, "n/a" Or " ") = C.Cells.Count Then ***
C.EntireColumn.Hidden = True
C.columnIndex = colIndex
Debug.Print colIndex
Else
C.EntireColumn.Hidden = False

End If

Next C


Application.ScreenUpdating = True

End Sub

Michael:
This has a very bad side effect. It makes all the rows I want hidden blank
including the data rows :-( . I will try the other suggestion but I think
it will not work since the OR is still needed for the sub-total rows.
Janis

"Michael" wrote:

Try this:

If Application.CountIf(C.Cells, "n/a") Or Application.CountIf(C.Cells, " ")
= .Cells.Count

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Janis" wrote:

Something is wrong with the OR statement on the line with the asterisks. I
want to hide the columns that have all n/a's as the only data cells. There
could be some blanks also but otherwise no data. Also, I was trying to debug
it and see what column it was counting. I can't tell if it works since my OR
statement is wrong.
tia,




For Each C In Intersect(Range("12:64000"), ActiveSheet.UsedRange).Columns
** If Application.CountIf(C.Cells, "n/a" Or " ") = C.Cells.Count Then ***
C.EntireColumn.Hidden = True
C.columnIndex = colIndex
Debug.Print colIndex
Else
C.EntireColumn.Hidden = False

End If

Next C


Application.ScreenUpdating = True

End Sub

Here it is again.

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
End If
Next C
Application.ScreenUpdating = True
End Sub



"Janis" wrote:

Something is wrong with the OR statement on the line with the asterisks. I
want to hide the columns that have all n/a's as the only data cells. There
could be some blanks also but otherwise no data. Also, I was trying to debug
it and see what column it was counting. I can't tell if it works since my OR
statement is wrong.
tia,




For Each C In Intersect(Range("12:64000"), ActiveSheet.UsedRange).Columns
** If Application.CountIf(C.Cells, "n/a" Or " ") = C.Cells.Count Then ***
C.EntireColumn.Hidden = True
C.columnIndex = colIndex
Debug.Print colIndex
Else
C.EntireColumn.Hidden = False

End If

Next C


Application.ScreenUpdating = True

End Sub

JLGWhiz
In that case it might be two problems. The blanks outside the used range
and the blanks inside the used range. There can be some blanks in the data
rows becuase there are sub-total rows. In this case the blank is inside the
data rows not outside the used range that is why I still need the OR
statement to count all n/a's or blanks in the used range.
How do I do the OR with the countif?
Janis

"JLGWhiz" wrote:

Here it is again.

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
End If
Next C
Application.ScreenUpdating = True
End Sub



"Janis" wrote:

Something is wrong with the OR statement on the line with the asterisks. I
want to hide the columns that have all n/a's as the only data cells. There
could be some blanks also but otherwise no data. Also, I was trying to debug
it and see what column it was counting. I can't tell if it works since my OR
statement is wrong.
tia,




For Each C In Intersect(Range("12:64000"), ActiveSheet.UsedRange).Columns
** If Application.CountIf(C.Cells, "n/a" Or " ") = C.Cells.Count Then ***
C.EntireColumn.Hidden = True
C.columnIndex = colIndex
Debug.Print colIndex
Else
C.EntireColumn.Hidden = False

End If

Next C


Application.ScreenUpdating = True

End Sub

tHANKS FOR TELLing me how to see the column address in the message box :-)

"JLGWhiz" wrote:

Here it is again.

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
End If
Next C
Application.ScreenUpdating = True
End Sub



"Janis" wrote:

Something is wrong with the OR statement on the line with the asterisks. I
want to hide the columns that have all n/a's as the only data cells. There
could be some blanks also but otherwise no data. Also, I was trying to debug
it and see what column it was counting. I can't tell if it works since my OR
statement is wrong.
tia,




For Each C In Intersect(Range("12:64000"), ActiveSheet.UsedRange).Columns
** If Application.CountIf(C.Cells, "n/a" Or " ") = C.Cells.Count Then ***
C.EntireColumn.Hidden = True
C.columnIndex = colIndex
Debug.Print colIndex
Else
C.EntireColumn.Hidden = False

End If

Next C


Application.ScreenUpdating = True

End Sub