Prev: Conditional Formatting v VBE code for desired effect
Next: String search on two tables
From: joemeshuggah on 27 May 2010 17:28
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"
From: joemeshuggah on 27 May 2010 17:51
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?

"ker_01" wrote:

> if(iserror(application.match(Cells(4, i).Value,HideArray,False))) then
> Range(ColRef & "1:" & ColRef & 65536).Hidden = True
> endif
>
> (aircode)
>
> "joemeshuggah" wrote:
>
> > 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"
From: Dave Peterson on 27 May 2010 19:56
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


joemeshuggah wrote:
>
> 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"

--

Dave Peterson
 | 
Pages: 1
Prev: Conditional Formatting v VBE code for desired effect
Next: String search on two tables