Code to hide Entire row based on criteria

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" <ram@discussions.microsoft.com> wrote in message
> news:A674D6C4-CAEA-4FEF-8105-DBDB27F0695C@microsoft.com...
> > 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" <ram@discussions.microsoft.com> wrote in message
> > > > news:3D2DC0D5-F406-45D7-BC4D-403A5D9D585E@microsoft.com...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > > >
>
>
>

Basically this is what it does

- insert a helper column in B
- create a formula in all B cells that tests if A of that row <> A1
- filter column B on the TRUE value
- create a range object pointing at the visible cells, that is those that
are not equal to A1
- delete the helper column (also clears the filter)
- hide all rows associated with the range object

I am afraid I have no idea on shared workbooks, never use them, they are
more trouble than they are work. The help topic 'Features that are
unavailable in shared workbooks says nothing about Autofilter that I can
see.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ram" <ram@discussions.microsoft.com> wrote in message
news:26EBA008-0808-4B7E-833A-90402E1AD903@microsoft.com...
> 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" <ram@discussions.microsoft.com> wrote in message
> > news:A674D6C4-CAEA-4FEF-8105-DBDB27F0695C@microsoft.com...
> > > 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" <ram@discussions.microsoft.com> wrote in message
> > > > > news:3D2DC0D5-F406-45D7-BC4D-403A5D9D585E@microsoft.com...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> >
> >
> >

Bob,
Can you tell me how to adjust this so that it can verify multiple columns
with the same criteria? That is I have a name that may appear in one of 6
columns in a list, I want to hide all the rows that don't have that name in
it. I adjusted your original macro to get it to work, but I have not been
able to adjust this faster macro. Also, Could the helper column be in column
BZ for instance?
Thank you,
Dan

"Bob Phillips" wrote:

> Basically this is what it does
>
> - insert a helper column in B
> - create a formula in all B cells that tests if A of that row <> A1
> - filter column B on the TRUE value
> - create a range object pointing at the visible cells, that is those that
> are not equal to A1
> - delete the helper column (also clears the filter)
> - hide all rows associated with the range object
>
> I am afraid I have no idea on shared workbooks, never use them, they are
> more trouble than they are work. The help topic 'Features that are
> unavailable in shared workbooks says nothing about Autofilter that I can
> see.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "ram" <ram@discussions.microsoft.com> wrote in message
> news:26EBA008-0808-4B7E-833A-90402E1AD903@microsoft.com...
> > 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" <ram@discussions.microsoft.com> wrote in message
> > > news:A674D6C4-CAEA-4FEF-8105-DBDB27F0695C@microsoft.com...
> > > > 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" <ram@discussions.microsoft.com> wrote in message
> > > > > > news:3D2DC0D5-F406-45D7-BC4D-403A5D9D585E@microsoft.com...
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > >
> > >
> > >
>
>
>