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 |
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 |