Running Excel 365. Windows 10 machine.
So I think I have a few issues to sort out, I am working on a spreadsheet that will change the rows available to show (fill in) by 2 variables. I'm creating an Address Matrix for a programming file on another type of system and there are 2 versions of systems that we can choose from.
System 1 has two groups of 99 devices,
System 2 has two groups of 159 devices, but can have additional pairs of 159 devices added (up to 3 pairs)
I have 2 drop down lists (Data Validation) at the top of my page, the first one (G2), is the quantity of pairs of devices. Values are 1, 2, 3.
The second drop down list at the top of the page (J2), is the device quantity. Values are 99 and 159. (This drop down list is populated by a indirect reference, so if value in G2 is "1", ,both 99 and 159 are options, if G2 is "2" or "3", then only 159 is an option.
I have rows 1-12 as the Header/titles of the list. The list is in rows 13-971 and has a lot of automated things that fill in, drop down lists, and combining of things to fill other columns.
I would like for a G2 value of "1", and a J2 value of "99" to only show the headers (1-12), and rows 13-111, and 172-271.
A G2 value of "1" and a J2 value of "159" to only show the headers, and rows 13-332.
A G2 value of "2" and a J2 value of "159" to only show the headers and rows 13-652.
A G2 value of "3" and a J2 value of "159" to show all rows to 971.
There are 10 other sheets in the workbook, this should work for only the "Address Matrix" sheet, preferably automatically, but I can add buttons to the header space.
I have 2 "buttons" that call out VBA that will hide or show the rows on the sheet that do not have text in them that I run at the end to minimize the number or rows and make it easily readable (and easy to add to my blueprints). I would like for the unhide button to only unhide the rows in the range that the first part shows. These are simple VBA right now for the whole sheet (Shown below).
This is a big ask, and I'm not much of a VBA person. Most all of my work is done with formatting and equations in excel directly.
I made some named ranges for the areas of each type, but as I read more, I don't think they can be used to make this simpler. The sheet will be protected when I am completed, and the areas where choices can be picked, and text entered will be left un-locked, so anyone can come in later and make adjustments to the devices, or start a new project with it.
Thank you for any assistance,
Rob L.
So I think I have a few issues to sort out, I am working on a spreadsheet that will change the rows available to show (fill in) by 2 variables. I'm creating an Address Matrix for a programming file on another type of system and there are 2 versions of systems that we can choose from.
System 1 has two groups of 99 devices,
System 2 has two groups of 159 devices, but can have additional pairs of 159 devices added (up to 3 pairs)
I have 2 drop down lists (Data Validation) at the top of my page, the first one (G2), is the quantity of pairs of devices. Values are 1, 2, 3.
The second drop down list at the top of the page (J2), is the device quantity. Values are 99 and 159. (This drop down list is populated by a indirect reference, so if value in G2 is "1", ,both 99 and 159 are options, if G2 is "2" or "3", then only 159 is an option.
I have rows 1-12 as the Header/titles of the list. The list is in rows 13-971 and has a lot of automated things that fill in, drop down lists, and combining of things to fill other columns.
I would like for a G2 value of "1", and a J2 value of "99" to only show the headers (1-12), and rows 13-111, and 172-271.
A G2 value of "1" and a J2 value of "159" to only show the headers, and rows 13-332.
A G2 value of "2" and a J2 value of "159" to only show the headers and rows 13-652.
A G2 value of "3" and a J2 value of "159" to show all rows to 971.
There are 10 other sheets in the workbook, this should work for only the "Address Matrix" sheet, preferably automatically, but I can add buttons to the header space.
I have 2 "buttons" that call out VBA that will hide or show the rows on the sheet that do not have text in them that I run at the end to minimize the number or rows and make it easily readable (and easy to add to my blueprints). I would like for the unhide button to only unhide the rows in the range that the first part shows. These are simple VBA right now for the whole sheet (Shown below).
This is a big ask, and I'm not much of a VBA person. Most all of my work is done with formatting and equations in excel directly.
VBA Code:
Sub HideRows()
Dim Rw As Long
Application.ScreenUpdating = False
Range("A13:A971").EntireRow.Hidden = False
For Rw = 13 To 971
If (Cells(Rw, 8) = "") And (Cells(Rw - 1, 8) = "") Then Cells(Rw, 8).EntireRow.Hidden = True
Next Rw
Application.ScreenUpdating = True
End Sub
Sub UnHideRows()
Application.ScreenUpdating = False
Range("A13:A971").EntireRow.Hidden = False
Application.ScreenUpdating = True
End SubI made some named ranges for the areas of each type, but as I read more, I don't think they can be used to make this simpler. The sheet will be protected when I am completed, and the areas where choices can be picked, and text entered will be left un-locked, so anyone can come in later and make adjustments to the devices, or start a new project with it.
Thank you for any assistance,
Rob L.
