Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Three tables on one worksheet, need to hide rows
Despite my limitation with writing codes, I need to find a simple to follow
code to hide rows without information in a specific column in three tables that I have on a single spreadsheet. The information on all these tables comes from another spreadsheet within the workbook hence upon changed to it, the information in my tables also changes. The table ranges are A67:I139, A171:I243 and A275-I347 They have the same number of rows but varying number of columns due to some merged columns. I want to hide rows that are blank in column 2 (in other words, display rows that are non-blank in column 2 in all the tables. Is this something that I can do with a code. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Three tables on one worksheet, need to hide rows
One way:
option explicit sub testme01() dim myAddresses as variant dim myCell as range dim iCtr as long myAddresses = array("b67:b139","b171:b243","B275:b347") with worksheets("sheet99") for ictr = lbound(myaddresses) to ubound(myaddresses) for each mycell in .range(myaddresses(ictr)).cells mycell.entirerow.hidden = (mycell.value = "") next mycell next ictr end with end sub Chiku wrote: Despite my limitation with writing codes, I need to find a simple to follow code to hide rows without information in a specific column in three tables that I have on a single spreadsheet. The information on all these tables comes from another spreadsheet within the workbook hence upon changed to it, the information in my tables also changes. The table ranges are A67:I139, A171:I243 and A275-I347 They have the same number of rows but varying number of columns due to some merged columns. I want to hide rows that are blank in column 2 (in other words, display rows that are non-blank in column 2 in all the tables. Is this something that I can do with a code. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Three tables on one worksheet, need to hide rows
Dave thanks for the code, do you mind telling me what it says - I apologize
that my knowledge of codes is limited, but once I am told what it means, I am able to follow and troubleshoot any problems I might have. "Dave Peterson" wrote: One way: option explicit sub testme01() dim myAddresses as variant dim myCell as range dim iCtr as long myAddresses = array("b67:b139","b171:b243","B275:b347") with worksheets("sheet99") for ictr = lbound(myaddresses) to ubound(myaddresses) for each mycell in .range(myaddresses(ictr)).cells mycell.entirerow.hidden = (mycell.value = "") next mycell next ictr end with end sub Chiku wrote: Despite my limitation with writing codes, I need to find a simple to follow code to hide rows without information in a specific column in three tables that I have on a single spreadsheet. The information on all these tables comes from another spreadsheet within the workbook hence upon changed to it, the information in my tables also changes. The table ranges are A67:I139, A171:I243 and A275-I347 They have the same number of rows but varying number of columns due to some merged columns. I want to hide rows that are blank in column 2 (in other words, display rows that are non-blank in column 2 in all the tables. Is this something that I can do with a code. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Three tables on one worksheet, need to hide rows
It looks through each cell in each of those ranges. Then if that cell is "", it
hides the row. (mycell.value = "") will evaluate to true or false depending on what's in that cell. Chiku wrote: Dave thanks for the code, do you mind telling me what it says - I apologize that my knowledge of codes is limited, but once I am told what it means, I am able to follow and troubleshoot any problems I might have. "Dave Peterson" wrote: One way: option explicit sub testme01() dim myAddresses as variant dim myCell as range dim iCtr as long myAddresses = array("b67:b139","b171:b243","B275:b347") with worksheets("sheet99") for ictr = lbound(myaddresses) to ubound(myaddresses) for each mycell in .range(myaddresses(ictr)).cells mycell.entirerow.hidden = (mycell.value = "") next mycell next ictr end with end sub Chiku wrote: Despite my limitation with writing codes, I need to find a simple to follow code to hide rows without information in a specific column in three tables that I have on a single spreadsheet. The information on all these tables comes from another spreadsheet within the workbook hence upon changed to it, the information in my tables also changes. The table ranges are A67:I139, A171:I243 and A275-I347 They have the same number of rows but varying number of columns due to some merged columns. I want to hide rows that are blank in column 2 (in other words, display rows that are non-blank in column 2 in all the tables. Is this something that I can do with a code. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Three tables on one worksheet, need to hide rows
I copied the code as is, and when I ran it, I got a subscription out of range
error. How do I fix that? Does it make any difference that one of the tables has a merged column B & C? "Dave Peterson" wrote: It looks through each cell in each of those ranges. Then if that cell is "", it hides the row. (mycell.value = "") will evaluate to true or false depending on what's in that cell. Chiku wrote: Dave thanks for the code, do you mind telling me what it says - I apologize that my knowledge of codes is limited, but once I am told what it means, I am able to follow and troubleshoot any problems I might have. "Dave Peterson" wrote: One way: option explicit sub testme01() dim myAddresses as variant dim myCell as range dim iCtr as long myAddresses = array("b67:b139","b171:b243","B275:b347") with worksheets("sheet99") for ictr = lbound(myaddresses) to ubound(myaddresses) for each mycell in .range(myaddresses(ictr)).cells mycell.entirerow.hidden = (mycell.value = "") next mycell next ictr end with end sub Chiku wrote: Despite my limitation with writing codes, I need to find a simple to follow code to hide rows without information in a specific column in three tables that I have on a single spreadsheet. The information on all these tables comes from another spreadsheet within the workbook hence upon changed to it, the information in my tables also changes. The table ranges are A67:I139, A171:I243 and A275-I347 They have the same number of rows but varying number of columns due to some merged columns. I want to hide rows that are blank in column 2 (in other words, display rows that are non-blank in column 2 in all the tables. Is this something that I can do with a code. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Three tables on one worksheet, need to hide rows
I'm guessing that the error occurs on this line:
with worksheets("sheet99") Change Sheet99 to the correct name. Chiku wrote: I copied the code as is, and when I ran it, I got a subscription out of range error. How do I fix that? Does it make any difference that one of the tables has a merged column B & C? "Dave Peterson" wrote: It looks through each cell in each of those ranges. Then if that cell is "", it hides the row. (mycell.value = "") will evaluate to true or false depending on what's in that cell. Chiku wrote: Dave thanks for the code, do you mind telling me what it says - I apologize that my knowledge of codes is limited, but once I am told what it means, I am able to follow and troubleshoot any problems I might have. "Dave Peterson" wrote: One way: option explicit sub testme01() dim myAddresses as variant dim myCell as range dim iCtr as long myAddresses = array("b67:b139","b171:b243","B275:b347") with worksheets("sheet99") for ictr = lbound(myaddresses) to ubound(myaddresses) for each mycell in .range(myaddresses(ictr)).cells mycell.entirerow.hidden = (mycell.value = "") next mycell next ictr end with end sub Chiku wrote: Despite my limitation with writing codes, I need to find a simple to follow code to hide rows without information in a specific column in three tables that I have on a single spreadsheet. The information on all these tables comes from another spreadsheet within the workbook hence upon changed to it, the information in my tables also changes. The table ranges are A67:I139, A171:I243 and A275-I347 They have the same number of rows but varying number of columns due to some merged columns. I want to hide rows that are blank in column 2 (in other words, display rows that are non-blank in column 2 in all the tables. Is this something that I can do with a code. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Three tables on one worksheet, need to hide rows
I had done that, thought I looked at it again and corrected an error.
I re-ran the macro, but the spreadsheet flickers as though it is doing something but nothing happens still. I really hate to be a bother on this, if it is possible to even show you what I am working on, I will gladly send you a copy. Anything else you can think of that I am doing wrong? "Dave Peterson" wrote: I'm guessing that the error occurs on this line: with worksheets("sheet99") Change Sheet99 to the correct name. Chiku wrote: I copied the code as is, and when I ran it, I got a subscription out of range error. How do I fix that? Does it make any difference that one of the tables has a merged column B & C? "Dave Peterson" wrote: It looks through each cell in each of those ranges. Then if that cell is "", it hides the row. (mycell.value = "") will evaluate to true or false depending on what's in that cell. Chiku wrote: Dave thanks for the code, do you mind telling me what it says - I apologize that my knowledge of codes is limited, but once I am told what it means, I am able to follow and troubleshoot any problems I might have. "Dave Peterson" wrote: One way: option explicit sub testme01() dim myAddresses as variant dim myCell as range dim iCtr as long myAddresses = array("b67:b139","b171:b243","B275:b347") with worksheets("sheet99") for ictr = lbound(myaddresses) to ubound(myaddresses) for each mycell in .range(myaddresses(ictr)).cells mycell.entirerow.hidden = (mycell.value = "") next mycell next ictr end with end sub Chiku wrote: Despite my limitation with writing codes, I need to find a simple to follow code to hide rows without information in a specific column in three tables that I have on a single spreadsheet. The information on all these tables comes from another spreadsheet within the workbook hence upon changed to it, the information in my tables also changes. The table ranges are A67:I139, A171:I243 and A275-I347 They have the same number of rows but varying number of columns due to some merged columns. I want to hide rows that are blank in column 2 (in other words, display rows that are non-blank in column 2 in all the tables. Is this something that I can do with a code. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Three tables on one worksheet, need to hide rows
Dave, its doing something!! It's hiding all the rows that have the blank
value that are in between upto the last row with data - except that this row is not the last - how do I get it to look at all the rows within the range? A little step is better than nothing - I am ready thanking you. "Dave Peterson" wrote: I'm guessing that the error occurs on this line: with worksheets("sheet99") Change Sheet99 to the correct name. Chiku wrote: I copied the code as is, and when I ran it, I got a subscription out of range error. How do I fix that? Does it make any difference that one of the tables has a merged column B & C? "Dave Peterson" wrote: It looks through each cell in each of those ranges. Then if that cell is "", it hides the row. (mycell.value = "") will evaluate to true or false depending on what's in that cell. Chiku wrote: Dave thanks for the code, do you mind telling me what it says - I apologize that my knowledge of codes is limited, but once I am told what it means, I am able to follow and troubleshoot any problems I might have. "Dave Peterson" wrote: One way: option explicit sub testme01() dim myAddresses as variant dim myCell as range dim iCtr as long myAddresses = array("b67:b139","b171:b243","B275:b347") with worksheets("sheet99") for ictr = lbound(myaddresses) to ubound(myaddresses) for each mycell in .range(myaddresses(ictr)).cells mycell.entirerow.hidden = (mycell.value = "") next mycell next ictr end with end sub Chiku wrote: Despite my limitation with writing codes, I need to find a simple to follow code to hide rows without information in a specific column in three tables that I have on a single spreadsheet. The information on all these tables comes from another spreadsheet within the workbook hence upon changed to it, the information in my tables also changes. The table ranges are A67:I139, A171:I243 and A275-I347 They have the same number of rows but varying number of columns due to some merged columns. I want to hide rows that are blank in column 2 (in other words, display rows that are non-blank in column 2 in all the tables. Is this something that I can do with a code. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Three tables on one worksheet, need to hide rows
Make sure this line:
myAddresses = array("b67:b139","b171:b243","B275:b347") includes all your rows. You can even get by with: myAddresses = array("b67:b139,b171:b243,B275:b347") But you'll still have to make sure you include the rows you want. Chiku wrote: Dave, its doing something!! It's hiding all the rows that have the blank value that are in between upto the last row with data - except that this row is not the last - how do I get it to look at all the rows within the range? A little step is better than nothing - I am ready thanking you. "Dave Peterson" wrote: I'm guessing that the error occurs on this line: with worksheets("sheet99") Change Sheet99 to the correct name. Chiku wrote: I copied the code as is, and when I ran it, I got a subscription out of range error. How do I fix that? Does it make any difference that one of the tables has a merged column B & C? "Dave Peterson" wrote: It looks through each cell in each of those ranges. Then if that cell is "", it hides the row. (mycell.value = "") will evaluate to true or false depending on what's in that cell. Chiku wrote: Dave thanks for the code, do you mind telling me what it says - I apologize that my knowledge of codes is limited, but once I am told what it means, I am able to follow and troubleshoot any problems I might have. "Dave Peterson" wrote: One way: option explicit sub testme01() dim myAddresses as variant dim myCell as range dim iCtr as long myAddresses = array("b67:b139","b171:b243","B275:b347") with worksheets("sheet99") for ictr = lbound(myaddresses) to ubound(myaddresses) for each mycell in .range(myaddresses(ictr)).cells mycell.entirerow.hidden = (mycell.value = "") next mycell next ictr end with end sub Chiku wrote: Despite my limitation with writing codes, I need to find a simple to follow code to hide rows without information in a specific column in three tables that I have on a single spreadsheet. The information on all these tables comes from another spreadsheet within the workbook hence upon changed to it, the information in my tables also changes. The table ranges are A67:I139, A171:I243 and A275-I347 They have the same number of rows but varying number of columns due to some merged columns. I want to hide rows that are blank in column 2 (in other words, display rows that are non-blank in column 2 in all the tables. Is this something that I can do with a code. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Three tables on one worksheet, need to hide rows
I have pasted the code you gave me and for some reason, I still cannot get it
to look through all the rows. Am I missing something? And do I always have to run the macro each time I want to hide the rows or can I automate it - say everytime you click on the worksheet? It's under General macros on the worksheet Sub Hide_Blanks() Dim myAddresses As Variant Dim myCell As Range Dim iCtr As Long myAddresses = Array("b67:b140", "b171:b245", "b275:b350") With Worksheets("SERVICE CONFIRMATION") For iCtr = LBound(myAddresses) To UBound(myAddresses) For Each myCell In .Range(myAddresses(iCtr)).Cells myCell.EntireRow.Hidden = (myCell.Value = " ") Next myCell Next iCtr End With End Sub "Dave Peterson" wrote: Make sure this line: myAddresses = array("b67:b139","b171:b243","B275:b347") includes all your rows. You can even get by with: myAddresses = array("b67:b139,b171:b243,B275:b347") But you'll still have to make sure you include the rows you want. Chiku wrote: Dave, its doing something!! It's hiding all the rows that have the blank value that are in between upto the last row with data - except that this row is not the last - how do I get it to look at all the rows within the range? A little step is better than nothing - I am ready thanking you. "Dave Peterson" wrote: I'm guessing that the error occurs on this line: with worksheets("sheet99") Change Sheet99 to the correct name. Chiku wrote: I copied the code as is, and when I ran it, I got a subscription out of range error. How do I fix that? Does it make any difference that one of the tables has a merged column B & C? "Dave Peterson" wrote: It looks through each cell in each of those ranges. Then if that cell is "", it hides the row. (mycell.value = "") will evaluate to true or false depending on what's in that cell. Chiku wrote: Dave thanks for the code, do you mind telling me what it says - I apologize that my knowledge of codes is limited, but once I am told what it means, I am able to follow and troubleshoot any problems I might have. "Dave Peterson" wrote: One way: option explicit sub testme01() dim myAddresses as variant dim myCell as range dim iCtr as long myAddresses = array("b67:b139","b171:b243","B275:b347") with worksheets("sheet99") for ictr = lbound(myaddresses) to ubound(myaddresses) for each mycell in .range(myaddresses(ictr)).cells mycell.entirerow.hidden = (mycell.value = "") next mycell next ictr end with end sub Chiku wrote: Despite my limitation with writing codes, I need to find a simple to follow code to hide rows without information in a specific column in three tables that I have on a single spreadsheet. The information on all these tables comes from another spreadsheet within the workbook hence upon changed to it, the information in my tables also changes. The table ranges are A67:I139, A171:I243 and A275-I347 They have the same number of rows but varying number of columns due to some merged columns. I want to hide rows that are blank in column 2 (in other words, display rows that are non-blank in column 2 in all the tables. Is this something that I can do with a code. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Three tables on one worksheet, need to hide rows
What rows are missing?
And you changed this line: mycell.entirerow.hidden = (mycell.value = "") to: myCell.EntireRow.Hidden = (myCell.Value = " ") maybe it shouldn't have been changed. or maybe it should have been changed to: mycell.entirerow.hidden = (trim(mycell.value) = "") Depends on what's in those cells. Chiku wrote: I have pasted the code you gave me and for some reason, I still cannot get it to look through all the rows. Am I missing something? And do I always have to run the macro each time I want to hide the rows or can I automate it - say everytime you click on the worksheet? It's under General macros on the worksheet Sub Hide_Blanks() Dim myAddresses As Variant Dim myCell As Range Dim iCtr As Long myAddresses = Array("b67:b140", "b171:b245", "b275:b350") With Worksheets("SERVICE CONFIRMATION") For iCtr = LBound(myAddresses) To UBound(myAddresses) For Each myCell In .Range(myAddresses(iCtr)).Cells myCell.EntireRow.Hidden = (myCell.Value = " ") Next myCell Next iCtr End With End Sub "Dave Peterson" wrote: Make sure this line: myAddresses = array("b67:b139","b171:b243","B275:b347") includes all your rows. You can even get by with: myAddresses = array("b67:b139,b171:b243,B275:b347") But you'll still have to make sure you include the rows you want. Chiku wrote: Dave, its doing something!! It's hiding all the rows that have the blank value that are in between upto the last row with data - except that this row is not the last - how do I get it to look at all the rows within the range? A little step is better than nothing - I am ready thanking you. "Dave Peterson" wrote: I'm guessing that the error occurs on this line: with worksheets("sheet99") Change Sheet99 to the correct name. Chiku wrote: I copied the code as is, and when I ran it, I got a subscription out of range error. How do I fix that? Does it make any difference that one of the tables has a merged column B & C? "Dave Peterson" wrote: It looks through each cell in each of those ranges. Then if that cell is "", it hides the row. (mycell.value = "") will evaluate to true or false depending on what's in that cell. Chiku wrote: Dave thanks for the code, do you mind telling me what it says - I apologize that my knowledge of codes is limited, but once I am told what it means, I am able to follow and troubleshoot any problems I might have. "Dave Peterson" wrote: One way: option explicit sub testme01() dim myAddresses as variant dim myCell as range dim iCtr as long myAddresses = array("b67:b139","b171:b243","B275:b347") with worksheets("sheet99") for ictr = lbound(myaddresses) to ubound(myaddresses) for each mycell in .range(myaddresses(ictr)).cells mycell.entirerow.hidden = (mycell.value = "") next mycell next ictr end with end sub Chiku wrote: Despite my limitation with writing codes, I need to find a simple to follow code to hide rows without information in a specific column in three tables that I have on a single spreadsheet. The information on all these tables comes from another spreadsheet within the workbook hence upon changed to it, the information in my tables also changes. The table ranges are A67:I139, A171:I243 and A275-I347 They have the same number of rows but varying number of columns due to some merged columns. I want to hide rows that are blank in column 2 (in other words, display rows that are non-blank in column 2 in all the tables. Is this something that I can do with a code. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Three tables on one worksheet, need to hide rows
Dave - Your are the best - thank you very much. I think it had to do with
the cell content even though it was blank - some cells had text others had numbers and I had to find a way to make it consistent. Thanks a million - you have no idea how much this has helped. "Dave Peterson" wrote: What rows are missing? And you changed this line: mycell.entirerow.hidden = (mycell.value = "") to: myCell.EntireRow.Hidden = (myCell.Value = " ") maybe it shouldn't have been changed. or maybe it should have been changed to: mycell.entirerow.hidden = (trim(mycell.value) = "") Depends on what's in those cells. Chiku wrote: I have pasted the code you gave me and for some reason, I still cannot get it to look through all the rows. Am I missing something? And do I always have to run the macro each time I want to hide the rows or can I automate it - say everytime you click on the worksheet? It's under General macros on the worksheet Sub Hide_Blanks() Dim myAddresses As Variant Dim myCell As Range Dim iCtr As Long myAddresses = Array("b67:b140", "b171:b245", "b275:b350") With Worksheets("SERVICE CONFIRMATION") For iCtr = LBound(myAddresses) To UBound(myAddresses) For Each myCell In .Range(myAddresses(iCtr)).Cells myCell.EntireRow.Hidden = (myCell.Value = " ") Next myCell Next iCtr End With End Sub "Dave Peterson" wrote: Make sure this line: myAddresses = array("b67:b139","b171:b243","B275:b347") includes all your rows. You can even get by with: myAddresses = array("b67:b139,b171:b243,B275:b347") But you'll still have to make sure you include the rows you want. Chiku wrote: Dave, its doing something!! It's hiding all the rows that have the blank value that are in between upto the last row with data - except that this row is not the last - how do I get it to look at all the rows within the range? A little step is better than nothing - I am ready thanking you. "Dave Peterson" wrote: I'm guessing that the error occurs on this line: with worksheets("sheet99") Change Sheet99 to the correct name. Chiku wrote: I copied the code as is, and when I ran it, I got a subscription out of range error. How do I fix that? Does it make any difference that one of the tables has a merged column B & C? "Dave Peterson" wrote: It looks through each cell in each of those ranges. Then if that cell is "", it hides the row. (mycell.value = "") will evaluate to true or false depending on what's in that cell. Chiku wrote: Dave thanks for the code, do you mind telling me what it says - I apologize that my knowledge of codes is limited, but once I am told what it means, I am able to follow and troubleshoot any problems I might have. "Dave Peterson" wrote: One way: option explicit sub testme01() dim myAddresses as variant dim myCell as range dim iCtr as long myAddresses = array("b67:b139","b171:b243","B275:b347") with worksheets("sheet99") for ictr = lbound(myaddresses) to ubound(myaddresses) for each mycell in .range(myaddresses(ictr)).cells mycell.entirerow.hidden = (mycell.value = "") next mycell next ictr end with end sub Chiku wrote: Despite my limitation with writing codes, I need to find a simple to follow code to hide rows without information in a specific column in three tables that I have on a single spreadsheet. The information on all these tables comes from another spreadsheet within the workbook hence upon changed to it, the information in my tables also changes. The table ranges are A67:I139, A171:I243 and A275-I347 They have the same number of rows but varying number of columns due to some merged columns. I want to hide rows that are blank in column 2 (in other words, display rows that are non-blank in column 2 in all the tables. Is this something that I can do with a code. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Three tables on one worksheet, need to hide rows
Glad you got it working!
Chiku wrote: Dave - Your are the best - thank you very much. I think it had to do with the cell content even though it was blank - some cells had text others had numbers and I had to find a way to make it consistent. Thanks a million - you have no idea how much this has helped. "Dave Peterson" wrote: What rows are missing? And you changed this line: mycell.entirerow.hidden = (mycell.value = "") to: myCell.EntireRow.Hidden = (myCell.Value = " ") maybe it shouldn't have been changed. or maybe it should have been changed to: mycell.entirerow.hidden = (trim(mycell.value) = "") Depends on what's in those cells. Chiku wrote: I have pasted the code you gave me and for some reason, I still cannot get it to look through all the rows. Am I missing something? And do I always have to run the macro each time I want to hide the rows or can I automate it - say everytime you click on the worksheet? It's under General macros on the worksheet Sub Hide_Blanks() Dim myAddresses As Variant Dim myCell As Range Dim iCtr As Long myAddresses = Array("b67:b140", "b171:b245", "b275:b350") With Worksheets("SERVICE CONFIRMATION") For iCtr = LBound(myAddresses) To UBound(myAddresses) For Each myCell In .Range(myAddresses(iCtr)).Cells myCell.EntireRow.Hidden = (myCell.Value = " ") Next myCell Next iCtr End With End Sub "Dave Peterson" wrote: Make sure this line: myAddresses = array("b67:b139","b171:b243","B275:b347") includes all your rows. You can even get by with: myAddresses = array("b67:b139,b171:b243,B275:b347") But you'll still have to make sure you include the rows you want. Chiku wrote: Dave, its doing something!! It's hiding all the rows that have the blank value that are in between upto the last row with data - except that this row is not the last - how do I get it to look at all the rows within the range? A little step is better than nothing - I am ready thanking you. "Dave Peterson" wrote: I'm guessing that the error occurs on this line: with worksheets("sheet99") Change Sheet99 to the correct name. Chiku wrote: I copied the code as is, and when I ran it, I got a subscription out of range error. How do I fix that? Does it make any difference that one of the tables has a merged column B & C? "Dave Peterson" wrote: It looks through each cell in each of those ranges. Then if that cell is "", it hides the row. (mycell.value = "") will evaluate to true or false depending on what's in that cell. Chiku wrote: Dave thanks for the code, do you mind telling me what it says - I apologize that my knowledge of codes is limited, but once I am told what it means, I am able to follow and troubleshoot any problems I might have. "Dave Peterson" wrote: One way: option explicit sub testme01() dim myAddresses as variant dim myCell as range dim iCtr as long myAddresses = array("b67:b139","b171:b243","B275:b347") with worksheets("sheet99") for ictr = lbound(myaddresses) to ubound(myaddresses) for each mycell in .range(myaddresses(ictr)).cells mycell.entirerow.hidden = (mycell.value = "") next mycell next ictr end with end sub Chiku wrote: Despite my limitation with writing codes, I need to find a simple to follow code to hide rows without information in a specific column in three tables that I have on a single spreadsheet. The information on all these tables comes from another spreadsheet within the workbook hence upon changed to it, the information in my tables also changes. The table ranges are A67:I139, A171:I243 and A275-I347 They have the same number of rows but varying number of columns due to some merged columns. I want to hide rows that are blank in column 2 (in other words, display rows that are non-blank in column 2 in all the tables. Is this something that I can do with a code. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I transpose rows to columns in a large worksheet? | Excel Discussion (Misc queries) | |||
What is fastest for this? The Small VBA or many Worksheet Functions...? | Excel Worksheet Functions | |||
Protect Worksheet but allow to insert or delete rows | Excel Discussion (Misc queries) | |||
Display specific rows from table on other worksheet | Excel Discussion (Misc queries) | |||
how to hide rows in a protected sheet | Excel Worksheet Functions |