not in array

  • Thread starter Thread starter joemeshuggah
  • Start date Start date
J

joemeshuggah

is there a way to loop through cells and hide them if the cell value is not
in an array?

eg.

HideArray = Array("Test","Test2")

For i = LC To 4 Step -1
Cells(4, i).Select
ColRef = Left(ActiveCell.Address(0, 0), (ActiveCell.Column < 27) + 2)
If Cells(4, i).Value <> (HideArray) Then
Range(ColRef & "1:" & ColRef & 65536).Hidden = True
End If

i get an error message on the line of code " If Cells(4, i).Value <>
(HideArray) Then"
 
Thanks!

Range(ColRef & "1:" & ColRef & 65536).Hidden = True
doesnt seem to work...it works when I change it to
Range(ColRef & "1:" & ColRef & 65536).ColumnWidth = 0

is this the equivalent of hiding (e.g. will not impact original column
widths when unhidden?
 
Option Explicit
Sub testme02()

Dim HideArray As Variant
Dim res As Variant
Dim LC As Long
Dim i As Long

HideArray = Array("Test", "Test2")

LC = 25 'testing

With ActiveSheet
For i = LC To 4 Step -1
res = Application.Match(.Cells(4, i), HideArray, 0)
If IsError(res) Then
'not found in array
.Columns(i).Hidden = True
End If
Next i
End With

End Sub
 
Back
Top