View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Janis Janis is offline
external usenet poster
 
Posts: 360
Default OR statement in a Countif

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