Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy entire row to another sheet based on a criteria | Excel Discussion (Misc queries) | |||
Hide/Delete entire rows based in the content of one cell | Excel Discussion (Misc queries) | |||
Copying an entire row or Rows based on column criteria | Excel Programming | |||
Deleting entire rows based on certain criteria | Excel Programming | |||
How do you hide a row based upon cell criteria? | Excel Programming |