OR statement in a Countif

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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
 
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
 
Back
Top