Define a Range for every N rows and do something if the cell is selected

[ongcaps]

Hi Community,

I am using Excel 2010.

I am trying to Hide/Unhide rows when specific cells are selected in a Worksheet. I have managed to do so with the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim UnHideRange As Range
Dim HideRange As Range

Set UnHideRange = Range("C16:E16,c27:e27")
Set HideRange = Range("G17,G28")

If Target.Cells.Count > 1 Then Exit Sub

If Not Application.Intersect(Target, HideRange) Is Nothing Then Call Hide10Cells

If Not Application.Intersect(Target, UnHideRange) Is Nothing Then Call Show10Cells

End Sub

As you see, I have defined two ranges (UnHideRange and HideRange) that define the cells that trigger the macros. Both ranges are repeated every 11 rows in the same columns. For example, HideRange would refer to cells G17, G28, G39, G50.... and UnhideRange would refer to C16:E16, c27:e27, C38:E38,c49:e49...

I do not want to enter manually all the ranges for two reasons:

1. They are a lot
2. If I add one row I would need to change everything manually

I would like to set the Range HideRange saying "it starts at cell G17 and then continues every 11 rows in the same column until row 500". The same for UnHideRange.

Is this possible?

Thank you in advance.

Best,
Daniel

 

[My Aswer Is This]

You said:

"For example, HideRange would refer to cells G17, G28, G39, G50.... and UnhideRange would refer to C16:E16, c27:e27, C38:E38,c49:e49...

These ranges are portions of a row.

You cannot hide portions of a row. You must hide the entire row or non of the row.

Please explain again in words what your wanting to do.

You should tell us:

I want to hide rows 12:20, 25:40 etc
Excel hides rows not cells.

 

[MickG]

Try this :-
By selecting "A1" the code will Hide Every 11th row from Row 17 on to row 500.
Reselect "A1" to Unhide rows.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Application.ScreenUpdating = False
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] n = 510 To 17 [COLOR="Navy"]Step[/COLOR] -1
          [COLOR="Navy"]If[/COLOR] (Range("A" & n).Row - 6) Mod 11 = 0 [COLOR="Navy"]Then[/COLOR]
                Range("A" & n).Rows.Hidden = IIf(Range("A" & n).Rows.Hidden = True, False, True)
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] If
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mikerickson]

How about

With Target
    If 1 < .Cells.Count Then Exit Sub

    If 3 <= .Column And .Column <= 5 And 16 <= .Row And ((.Row Mod 11) = (16 Mod 11)) Then
        Call Show10Cells
    ElseIf .Column = 7 And 17 <= .Row And ((.Row Mod 11) = (17 Mod 11)) Then
        Call Hide10Cells
    End If
End With

 

[ongcaps]

You said:

"For example, HideRange would refer to cells G17, G28, G39, G50.... and UnhideRange would refer to C16:E16, c27:e27, C38:E38,c49:e49...

These ranges are portions of a row.

You cannot hide portions of a row. You must hide the entire row or non of the row.

Please explain again in words what your wanting to do.

You should tell us:

I want to hide rows 12:20, 25:40 etc
Excel hides rows not cells.

Hi,

First of all thank you very much for your quick answers and I am sorry if I did not explain the problem properly.

HideRange and UnHideRange refers to cells that if the user select, will trigger the macro Hide10Cells or Unhide10Cells that will hide/Unhide 10 rows below the selected cell. For example, if the user select G17, rows 17-26 will be hidden (yes, including the selected cell). If then the user selects C16 OR D16 OR E16, these same rows will be unhidden. It is a way to show/hide a table.

My problem is that this table is repeated multiple times and I was looking for some way to avoid entering manually G17, G28....

Thanks.

Daniel

 

[My Aswer Is This]

Thanks Daniel. I see two more people have provided answers to you and you have not responded as to if what they have provided works for you. So you need to respond to those and let us know what works and does not work.

Hi,

First of all thank you very much for your quick answers and I am sorry if I did not explain the problem properly.

HideRange and UnHideRange refers to cells that if the user select, will trigger the macro Hide10Cells or Unhide10Cells that will hide/Unhide 10 rows below the selected cell. For example, if the user select G17, rows 17-26 will be hidden (yes, including the selected cell). If then the user selects C16 OR D16 OR E16, these same rows will be unhidden. It is a way to show/hide a table.

My problem is that this table is repeated multiple times and I was looking for some way to avoid entering manually G17, G28....

Thanks.

Daniel

 

[ongcaps]

Hi,

Thank you for your responses and help. It took some time to respond because I was trying to find the perfect solution for my needs. I finally created the range with vba and then I used this range in the selection event. Here is the code:

Sub TrainingHide()
    Dim rng As Range
    Dim RngNth As Range
    Dim LstRw As Long

    With ActiveSheet
        Set rng = .Range("h17", "h235")
    End With

    For LstRw = 1 To rng.Rows.Count Step 11
        If LstRw = 1 Then
            Set RngNth = rng(LstRw, 1)
        Else
            Set RngNth = Union(rng(LstRw, 1), RngNth)
        End If
    Next LstRw

    RngNth.Name = "TrainingHide"

End Sub

'Second part

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    TrainingHide
    If Target.Cells.Count > 2 Then Exit Sub
    If Not Intersect(ActiveCell, Range("TrainingHide")) Is Nothing Then
        Call Hide10Cells
    End If
End Sub

Best,
Daniel

 

[ongcaps]

By the way, Davesexcel helped me with this solution. I do not want to take credit for something i can not do....