need an OR in the countif statement

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

Guest

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
 
I haven't tested this but it should work:

If Application.CountIf(C.Cells, "n/a") + Application.CountIf(C.Cells, "
") _
= C.Cells.Count Then
 
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")
 
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.
 
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.
 

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

Back
Top