Code to hide Entire row based on criteria

I would like vb code to perform the following:

Look through all the used rows and compare the values in column A. If the
value in column A does not equal the value in Range("$A$1") Then
hide entire row.

Thanks for any help

For i = 1 to Cells(Rows.Count,"A").End(xlUp).Row
If Cells(i,"A").Value <> Range("A1").Value Then
Rows(i).Hidden = True
End If
Next i

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ram" <[email protected]> wrote in message
news:[email protected]...
> I would like vb code to perform the following:
>
> Look through all the used rows and compare the values in column A. If the
> value in column A does not equal the value in Range("$A$1") Then
> hide entire row.
>
> Thanks for any help
>
>

Hi Bob,

Your code is working, however I have 20 thousand rows and it takes a long
time to hide each row that dosen't ="A1".

Do you know how I could filter the rows based on A1 that would be faster.
This is a shared workbook so when I used advanced filter I received a run tme
error.

Thanks fro any help

"Bob Phillips" wrote:

>
> For i = 1 to Cells(Rows.Count,"A").End(xlUp).Row
> If Cells(i,"A").Value <> Range("A1").Value Then
> Rows(i).Hidden = True
> End If
> Next i
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "ram" <[email protected]> wrote in message
> news:[email protected]...
> > I would like vb code to perform the following:
> >
> > Look through all the used rows and compare the values in column A. If the
> > value in column A does not equal the value in Range("$A$1") Then
> > hide entire row.
> >
> > Thanks for any help
> >
> >
>
>
>

I tried to use a with statement but it didn't work ( I don't think I have the
syntax correct)
Do you know if this would even help?

I'm trying to solve the following problem:

I have a table where I use advance filter to show only agent names where
column A equals the value of column A1. This works until I share the workbook
when I share the workbook I get runtime errror 1004.

To get around the error I asked and received code that would allow me to
hide all rows where the value in column A did not = the vlaue in A1. The code
works fine however it is slow when I try to hide 20 thousand rows of data.

Any suggestion on how I might solve my problem would be very helpful.

Thanks for all your help


"ram" wrote:

> Hi Bob,
>
> Your code is working, however I have 20 thousand rows and it takes a long
> time to hide each row that dosen't ="A1".
>
> Do you know how I could filter the rows based on A1 that would be faster.
> This is a shared workbook so when I used advanced filter I received a run tme
> error.
>
> Thanks fro any help
>
> "Bob Phillips" wrote:
>
> >
> > For i = 1 to Cells(Rows.Count,"A").End(xlUp).Row
> > If Cells(i,"A").Value <> Range("A1").Value Then
> > Rows(i).Hidden = True
> > End If
> > Next i
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "ram" <[email protected]> wrote in message
> > news:[email protected]...
> > > I would like vb code to perform the following:
> > >
> > > Look through all the used rows and compare the values in column A. If the
> > > value in column A does not equal the value in Range("$A$1") Then
> > > hide entire row.
> > >
> > > Thanks for any help
> > >
> > >
> >
> >
> >

This should be quicker

Sub HideRows()
Dim iLastRow As Long
Dim rng As Range
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Columns(2).Insert
Range("B1").Value = "TEMP"
Range("B2").Resize(iLastRow - 1).Formula = "=A2<>$A$1"
Columns("B:B").AutoFilter field:=1, Criteria1:="TRUE"
Set rng = Range("A2").Resize(iLastRow -
1).SpecialCells(xlCellTypeVisible)
Columns("B:B").Delete
rng.EntireRow.Hidden = True
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ram" <[email protected]> wrote in message
news:[email protected]...
> I tried to use a with statement but it didn't work ( I don't think I have
the
> syntax correct)
> Do you know if this would even help?
>
> I'm trying to solve the following problem:
>
> I have a table where I use advance filter to show only agent names where
> column A equals the value of column A1. This works until I share the
workbook
> when I share the workbook I get runtime errror 1004.
>
> To get around the error I asked and received code that would allow me to
> hide all rows where the value in column A did not = the vlaue in A1. The
code
> works fine however it is slow when I try to hide 20 thousand rows of data.
>
> Any suggestion on how I might solve my problem would be very helpful.
>
> Thanks for all your help
>
>
> "ram" wrote:
>
> > Hi Bob,
> >
> > Your code is working, however I have 20 thousand rows and it takes a
long
> > time to hide each row that dosen't ="A1".
> >
> > Do you know how I could filter the rows based on A1 that would be
faster.
> > This is a shared workbook so when I used advanced filter I received a
run tme
> > error.
> >
> > Thanks fro any help
> >
> > "Bob Phillips" wrote:
> >
> > >
> > > For i = 1 to Cells(Rows.Count,"A").End(xlUp).Row
> > > If Cells(i,"A").Value <> Range("A1").Value Then
> > > Rows(i).Hidden = True
> > > End If
> > > Next i
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "ram" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I would like vb code to perform the following:
> > > >
> > > > Look through all the used rows and compare the values in column A.
If the
> > > > value in column A does not equal the value in Range("$A$1") Then
> > > > hide entire row.
> > > >
> > > > Thanks for any help
> > > >
> > > >
> > >
> > >
> > >

Hi Bob,

Thanks so much for the code, this is real fast.
I'm trying to understand how you did this
Insert A TEMP column (2)
Then put a 1 in column B if A doesn't = A1
Then autofilter column B where criteria =1
Then delete column B

Would this mean that autofilter works in shared workbooks howerever advanced
filter will not?

I have so much to learn.

Thanks again for all your help.

"Bob Phillips" wrote:

> This should be quicker
>
> Sub HideRows()
> Dim iLastRow As Long
> Dim rng As Range
> iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> Columns(2).Insert
> Range("B1").Value = "TEMP"
> Range("B2").Resize(iLastRow - 1).Formula = "=A2<>$A$1"
> Columns("B:B").AutoFilter field:=1, Criteria1:="TRUE"
> Set rng = Range("A2").Resize(iLastRow -
> 1).SpecialCells(xlCellTypeVisible)
> Columns("B:B").Delete
> rng.EntireRow.Hidden = True
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "ram" <[email protected]> wrote in message
> news:[email protected]...
> > I tried to use a with statement but it didn't work ( I don't think I have
> the
> > syntax correct)
> > Do you know if this would even help?
> >
> > I'm trying to solve the following problem:
> >
> > I have a table where I use advance filter to show only agent names where
> > column A equals the value of column A1. This works until I share the
> workbook
> > when I share the workbook I get runtime errror 1004.
> >
> > To get around the error I asked and received code that would allow me to
> > hide all rows where the value in column A did not = the vlaue in A1. The
> code
> > works fine however it is slow when I try to hide 20 thousand rows of data.
> >
> > Any suggestion on how I might solve my problem would be very helpful.
> >
> > Thanks for all your help
> >
> >
> > "ram" wrote:
> >
> > > Hi Bob,
> > >
> > > Your code is working, however I have 20 thousand rows and it takes a
> long
> > > time to hide each row that dosen't ="A1".
> > >
> > > Do you know how I could filter the rows based on A1 that would be
> faster.
> > > This is a shared workbook so when I used advanced filter I received a
> run tme
> > > error.
> > >
> > > Thanks fro any help
> > >
> > > "Bob Phillips" wrote:
> > >
> > > >
> > > > For i = 1 to Cells(Rows.Count,"A").End(xlUp).Row
> > > > If Cells(i,"A").Value <> Range("A1").Value Then
> > > > Rows(i).Hidden = True
> > > > End If
> > > > Next i
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "ram" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I would like vb code to perform the following:
> > > > >
> > > > > Look through all the used rows and compare the values in column A.
> If the
> > > > > value in column A does not equal the value in Range("$A$1") Then
> > > > > hide entire row.
> > > > >
> > > > > Thanks for any help
> > > > >
> > > > >
> > > >
> > > >
> > > >
>
>
>