calculate Range (X:y) based on a calculated X value for hiding rows

[biggjake]

I am trying to figure out how to calculate the beginning or end of a range of rows to hide using the VB code.
I have a spreadsheet cell (A3) that has an input of 1-10.
Based upon that input, there is a section of rows (A10-A19) with values that should show only the number of rows based on that the input.
i.e. if A3=1, then only row 10 should show and 11-19 should be hidden. If A3=2, then 10 thru 11 should show and 12-19 should be hidden, etc.
Can I use the following format? If so, how do I get the ranges to work as a calculation based on A3?

Sheets("System Information").Range("10:???").EntireRow.Hidden = False
Sheets("System Information").Range("???:19").EntireRow.Hidden = True

Would the following work?

Range ("A3") =X
Sheets("System Information").Range("10:(10+X)").EntireRow.Hidden = False
Sheets("System Information").Range("(10+X+1):19").EntireRow.Hidden = True


Thanks in advance

[TMS]

Maybe:

Please Login or Register  to view this content.

[biggjake]

Thanks.
I had edited my original post but it doesn't look like it took.

My edited version:

I have a spreadsheet cell (A3) that has an input of 1-10.
Based upon that input, there are 2 sections of rows (10-19) and (25-34) with values that the row should show only the number of rows based on that the input.
i.e. if A3=1, then only row 10\25 should show and 11-19\26-34 should be hidden. If A3=2, then 10-11\25-26 should show and 12-19\27-34 should be hidden, etc.
Can I use the following format? If so, how do I get the ranges to work as a calculation based on A3?

Sheets("System Information").Range("10:???,25:???").EntireRow.Hidden = False
Sheets("System Information").Range("???:19,???:34").EntireRow.Hidden = True

Would the following work?

Range ("A3") =X
Sheets("System Information").Range("10:(10+X),25:(25+X)").EntireRow.Hidden = False
Sheets("System Information").Range("(10+X+1):19,(25+x+1):34").EntireRow.Hidden = True

[TMS]

Would the following work?

no

Look at the code I have offered. Repeat the bit from With .Range("A10") down to the first End With but change A10 to A25

[biggjake]

no

Look at the code I have offered. Repeat the bit from With .Range("A10") down to the first End With but change A10 to A25

Ok, but note i removed the A before the 10 since i am doing a row. Would yours then be: Range("10")

[TMS]

Would yours then be: Range("10")

no

A range refers to a column and row ... and then you have .EntireRow

Suggest you just try it. Right click on the tab that needs to run the code, select View Code and copy and paste the code there. Then change A3 and see what happens.

[biggjake]

I fully appreciate the help.
Sorry, I don't do this programming all the time so don't know all of it well.
It is erroring on the: If Intersect(Target, Range("E11")) Is Nothing Then Exit Sub

Here is the full code for the section I am working on. The Commented out Case Select is what I am trying to not have to do 10 times. There are several worksheets in the workbook with one that has all of the inputs and where the code is located. Note that e11 is a data validation box with 1 thru 10 listed. I was trying to get an idea on how to do it and then implement it, but that failed it seems.

Sub MultiDomain()
Select Case Range("E10").Value
Case Is = "N"
Range("e11").EntireRow.Hidden = True
Range("e11") = 0
Sheets("System Information").Range("B13:B22") = ""
Sheets("System Information").Range("122:131,139:148").EntireRow.Hidden = True
Sheets("DNS").Range("9:48,54:63,65:74,76:85,87:96,100:109,130:139,141:150,152:161,167:176,178:187").EntireRow.Hidden = True
Sheets("Certificates").Range("7:16,18:27,36:65,72:81,83:92,94:103,105:114,117:126").EntireRow.Hidden = True
If Range("e29") = 1 Then
Sheets("System Information").Range("149:149").EntireRow.Hidden = False
Sheets("DNS").Range("86:86,98:98,162:162").EntireRow.Hidden = False
Sheets("Certificates").Range("17:17,28:28,70:70").EntireRow.Hidden = False
Else
Sheets("System Information").Range("149:149").EntireRow.Hidden = True
Sheets("DNS").Range("86:86,98:98,162:162").EntireRow.Hidden = True
Sheets("Certificates").Range("17:17,28:28,70:70").EntireRow.Hidden = True
End If
Case Is = "Y"
AddDomains
End Select
End Sub
'#########Additional SIP Domains############
Sub AddDomains()

' Option Explicit
'
'Private Sub AddDomains(ByVal Target As Range)
' in worksheet class module for System Information worksheet
Dim lTV As Long

If Intersect(Target, Range("E11")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
If Target.Value < 1 Then Exit Sub
If Target.Value > 10 Then Exit Sub
lTV = Target.Value

With Me
With Sheets("System Information").Range("13:13")
.Resize(10).EntireRow.Hidden = False
On Error Resume Next
.Offset(lTV).EntireRow.Resize(10 - lTV).Hidden = True
On Error GoTo 0
End With
End With
'Select Case Range("e11").Value
' Case Is = 1
' Sheets("System Information").Range("B14:B22") = ""
' Sheets("System Information").Range("13:13").EntireRow.Hidden = False
' Sheets("System Information").Range("14:22").EntireRow.Hidden = True
' Sheets("DNS").Range("9:12,54:54,65:65,76:76,100:100,130:130,141:141,167:167,178:178").EntireRow.Hidden = False
' Sheets("DNS").Range("13:48,55:63,66:74,77:85,101:109,131:139,142:150,168:176,179:187").EntireRow.Hidden = True
' Sheets("Certificates").Range("7:7,36:38,71:72,82:83,104:105,117:117").EntireRow.Hidden = False
' Sheets("Certificates").Range("8:16,39:65,73:81,84:92,106:114,118:126").EntireRow.Hidden = True
' If Range("e29") = 1 Then
' Sheets("System Information").Range("122:122,139:139").EntireRow.Hidden = False
' Sheets("System Information").Range("123:131,140:148").EntireRow.Hidden = True
' Sheets("DNS").Range("87:87,98:98,152:152,162:162").EntireRow.Hidden = False
' Sheets("Certificates").Range("18:18,28:28,70:70,94:94").EntireRow.Hidden = False
' End If
' If Range("e29") = 2 Then
' Sheets("System Information").Range("122:122,139:139").EntireRow.Hidden = False
' Sheets("System Information").Range("123:131,140:148").EntireRow.Hidden = True
' Sheets("DNS").Range("87:87,152:152").EntireRow.Hidden = False
' Sheets("DNS").Range("98:98,162:162").EntireRow.Hidden = True 'STATIC ENTRY
' Sheets("Certificates").Range("18:18,94:94").EntireRow.Hidden = False
' Sheets("Certificates").Range("28:28,70:70").EntireRow.Hidden = True 'STATIC ENTRY
' End If
' If Range("e29") = 3 Then
' Sheets("System Information").Range("139:139").EntireRow.Hidden = False
' Sheets("System Information").Range("122:131,140:148").EntireRow.Hidden = True
' Sheets("DNS").Range("87:96,98:98,152:161,162:162").EntireRow.Hidden = True 'STATIC ENTRY
' Sheets("Certificates").Range("18:27,28:28,70:70,94:103").EntireRow.Hidden = True 'STATIC ENTRY
' End If
End Sub

Is the best\easiest I just need to write the one section 10 times?

thanks.

[TMS]

Target is only relevant in an event handler, hence why you get the error. It is not defined.

See the example workbook attached. I have put the code in place so that it works on the System Information worksheet. The code you have provided is much more complex than the question you have asked and I have not tried to address that. I would not know how to make that into generic code as I don't know what your rules are.

Regards, TMS

Please Login or Register  to view this content.