hide column script, range address?

[Janis]

The script runs through all the cells on a sheet looking for an #na, if
there is an
#na in all the rows in that column it seeks to hide the row. The loop works
and there are values in
x and hidecol. From running this in the debugger it looks like it goes
across the rows not down?

It however doesn't hide those columns even if the hidecol boolean is true.
I think it is because the address for x is wrong ? I tried selecting it
first and also entirecolumn = hidden and that didn't work.
Range(x).Select
columns(x).entirecolumn.hidden = true
tia,




Sub Format_VOD_HideColumns()

Dim UsedRange1 As Range
Dim UsedRows1 As Long
Dim UsedCol1 As Long
Dim x As Integer
Dim HideCol As Boolean
Dim C As Range

Set UsedRange1 = ActiveSheet.UsedRange
UsedRows1 = UsedRange1.Rows.Count
UsedCol1 = UsedRange1.Columns.Count

Application.ScreenUpdating = False

For x = 1 To UsedCol1
HideCol = True
For Each C In Intersect(Range(Cells(12, x), Cells(64000, x)), UsedRange1)

If C.Value <> "N/A" Then

HideCol = False
Exit For
End If
Next C
If HideCol = True Then


*** Columns(x).Hidden = True***


End If
Next x





Application.ScreenUpdating = True

End Sub

 

[Bernie Deitrick]

Janis,

Here's the correct way to do it - it rarely pays to loop through all the
cells of a range.

Sub Format_VOD_HideColumns2()
Dim C As Range

Application.ScreenUpdating = False

For Each C In Intersect(Range("12:64000"), 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

HTH,
Bernie
MS Excel MVP