VBA Help for Hiding rows from a data validation list choice.

realred2

New Member
Joined
Jun 22, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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.

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 Sub


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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

realred2

New Member
Joined
Jun 22, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Is anyone able to give me a direction? is there more information required? Any help would be appreciated.
 

Forum statistics

Threads
1,141,072
Messages
5,704,134
Members
421,328
Latest member
mippy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top