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

Code to hide Entire row based on criteria
 

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" wrote in message
...
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

Code to hide Entire row based on criteria
 

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" wrote in message
...
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

Code to hide Entire row based on criteria
 

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" wrote in message
...
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

Code to hide Entire row based on criteria
 

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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

Code to hide Entire row based on criteria
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Code to hide Entire row based on criteria
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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