How to define multiple non-contiguous ranges?

[scoha]

I want to define lots of ranges that are comprised of every 5th column (starting at Col D through to Col AV) from Row 9 to Row 200 for each one.

Is there an easy way to do this?

I want this so I can set up a "tick" column along the lines of:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

'This macro inserts a "tick" in a cell that is double clicked
'The tick is the letter "a" in the Martlett format group
'Range where this takes place is union of ranges "name ranges here" etc

Dim tickRange As Range

Set tickRange = Union([Range1], [Range2], [Range2])

    If Not Intersect(Target, tickRange) Is Nothing Then

            Cancel = True 'Prevent going into Edit Mode

            Target.Font.Name = "Marlett"

                If Target = vbNullString Then

                    Target = "a"

                Else

                    Target = vbNullString

                End If

    End If

End Sub

This all looks very inelegant - is there a neater way??

thanks in advance

 

[mikerickson]

The regularity of your discontinuous range allows for a test like this.

With Target
    If (.Column Mod 5) = 4 And (9 <= .Row) And (.Row <= 200) Then
        Rem your code
    End If
End With

But for a more random collection of Columns, You could use something like.

Set TickRange = Range("D1, I1, N1, M1, ...")
Set TickRange = Application.Intersect(Range("9:200"), TickRange.EntireColumn)

If Application.Intersect(Target, TickRange) Is Nothing Then
    Rem your code
End If

If you have an even uglier range to act on, you could take a completely different approach. Tag each cell that you want it to act on.

If Target.Validation.InputTitle = "action cell" Then

The bad thing about this approach is that each of the cells have to be tagged.
(Serves the developer right for using weird, irregular, non-column, non-row ranges. )
The nice thing about using .Validation.InputTitle for a tag is that it won't error, even if Validation hasn't been set for that cell.

 

[scoha]

Fabulous mickerikson,

once again a fountain of vba knowledge

great options

thanks