Hiding a variable RangeName based on value in other sheet

Question

At the moment I'm working on making a working code smaller using an array. I will explain the code shortly;

If a certain part is required to be in a datasheet (this worksheet is called "High Pressure Grinding Rolls"), then the user can define this by putting in value "a" on Sheets("Invulformulier"). Now there are several parts which can be on the datasheet if the cell value is "a". If we have "partA", "partB" and "partC", the RangeName of the cell will be the name of the part on Sheets("Invulformulier"). The RangeName of the range on Sheets("High Pressure Grinding Rolls") will be the name of the part + "1". For example "partA1". This range must be hidden depending on if the user puts in "a" for "partA".

This is the code I used and worked, but is specific to the cell names:

Sub Hidecellv1 ()

   If Range("partA").Value = "a" Then
      Sheets("High Pressure Grinding Rolls").Range("partA1").EntireRow.Hidden = False
   ElseIf Range("partA").Value = "" Then
      Sheets("High Pressure Grinding Rolls").Range("partA1").EntireRow.Hidden = True
   End If

End Sub

This code is very specific and I want to make an array. This is what I have so far:

Sub Hidecellwitharray ()    

    Dim rngName As Range
    Dim cell As Range

    Application.ScreenUpdating = False

    For Each cell In Range("Checkbox") 'Where user puts in value "a" or not
        If cell.Value = "a" Then
            Sheets("High Pressure Grinding Rolls").Range(RangeName & "1").EntireRow.Hidden = False

        Else
            Sheets("High Pressure Grinding Rolls").Range(RangeName & "1").EntireRow.Hidden = True
        End If
    Next cell
    Application.ScreenUpdating = True

End Sub

The searching for value "a" for every part works, but I can't get it to work to hide the parts in the datasheet if value "a" is or isn't inserted. How do I refer to a variable RangeName?

I'm not clear what RangeName is. Is it a named range on your sheet? — SJR, Jan 13 '17 at 11:45
No, I just used it because I thought it might refer to the name of a cell. — Jay, Jan 13 '17 at 11:54

score 1 · Accepted Answer · answered Jan 13 '17 at 12:00

1

If I correctly understood your issue you could try this:

Option Explicit

Sub Hidecellwitharray()
    Dim cell As Range

    Application.ScreenUpdating = False
    For Each cell In Range("Checkbox") 'Where user puts in value "a" or not
        Sheets("High Pressure Grinding Rolls").Range(Split(cell.Name.Name, "!")(1) & "1").EntireRow.Hidden = Not cell.Value = "a"
    Next cell
    Application.ScreenUpdating = True
End Sub

answered Jan 13 '17 at 12:00

user3598756

28,893
4
18
28

I think you correctly understand the issue. When I run your script it states: "Run-time error '9': Subscript out of range". Please explain a bit what is going on at the "Range(Split..." section. – Jay Jan 13 '17 at 12:08
1) `cell.Name.Name` is returning something like "Invulformulier!partC". 2)`Split(cell.Name.Name, "!")` is dividing that string into an array assuming "|" as delimiter 3) the second index of that array returns "partC" 4) 3) `& "1"` returns "partC1". 4) `Sheets("High Pressure Grinding Rolls").Range( 3) )` returns the wanted range – user3598756 Jan 13 '17 at 12:19
Hmm ok. I understand the code, but I don't know why the error occurs. All the names are correct. – Jay Jan 13 '17 at 12:24
I've tried the replace function instead. So far this works! Thanks for the concept! – Jay Jan 13 '17 at 12:49
This is what I used: "Range(Replace(cell.Name.Name, cell.Name.Name, cell.Name.Name & "1")).EntireRow..." – Jay Jan 13 '17 at 12:52
You are welcome. But since my code worked in my tests, could you tell me what's the returned value of `?cell.Name.Name` in your Immediate Window when you place e break point at `Sheets("H...`? – user3598756 Jan 13 '17 at 13:56
Ok, I tried the file at home and your code worked. I think some ranges or something were wrong at work. However, the cell.Name.Name returns the correct name value of the cell. The code is fine. – Jay Jan 13 '17 at 20:18
Thank you for the feedback. Good coding! – user3598756 Jan 13 '17 at 21:32

score 0 · Answer 2 · edited May 23 '17 at 11:52

0

Does this do as you require? It will hide all named ranges on the 'High Pressure Grinding Rolls' sheet, then show the row containing the corresponding checkbox value.

I found helpful information on the following page: Loop through all named ranges in a Excel Sheet

 Sub Hidecellv1()

     Dim nm
     Dim rngName   

     For Each nm In ThisWorkbook.Names

         If Left(nm.Name, 4) = "Part" Then
         Sheets("High Pressure Grinding Rolls").Range(nm).EntireRow.Hidden = True
         End If

    Next nm   

    rngName = Range("checkbox").Value

    Sheets("High Pressure Grinding Rolls").Range("Part" & rngName & "1").EntireRow.Hidden = False

End Sub

edited May 23 '17 at 11:52

Community

1
1

answered Jan 13 '17 at 11:50

5202456

966
14
24

What is important is that the checkbox values are leading. They decide whether a certain part is hidden on the "High Pressure Grinding Rolls" sheet. So if the value for partA is "a" then that part is not hidden on the sheet "High Pressure...". What is also important is that the name of a range isn't "part" all the time, it could be a lot of things. – Jay Jan 13 '17 at 12:00

Hiding a variable RangeName based on value in other sheet

2 Answers2