OFFSETting non-contiguous un named ranges.

[rtirado]

Hello,
I am attempting to offset a range. Because the range is non-contiguous the code I have come up with will not work.

Private Sub CommandButton1_Click()
Range(Selection.Offset(0, 3), Selection.Offset(0, -3)).Select
End Sub

When my range is selected and I hit the button instead of offsetting every cell highlighted the offset stops at the first non highlighted cell.

I have no experience with VBA except what I could find on-line.

I would greatly appreciate any suggestions.

 

[Takae]

Hope this helps.

Private Sub CommandButton1_Click()
Dim rng As Range, c, nrng As Range
Set rng = Range(Selection, cells(Selection.Row, Columns.Count))
For Each c In rng
    If c.Interior.ColorIndex = xlNone Then
        Set nrng = Range(Selection, cells(c.Row, c.Column))
    Else
        Exit For
    End If
Next
 nrng.Select
 End Sub

 

[footoo]

Dim area As Range, rng As Range
If Not Intersect(Selection, [A:C]) Is Nothing Then
    MsgBox "Do not select in columns A:C"
    Exit Sub
End If
For Each area In Selection.Areas
    If rng Is Nothing Then
        Set rng = Range(area.Offset(0, 3), area.Offset(0, -3))
    Else
        Set rng = Union(rng, Range(area.Offset(0, 3), area.Offset(0, -3)))
    End If
Next
rng.Select

 

[rtirado]

Hello Takae. Thank you for you quick response. The code is doing the same thing mine was doing before its just grabbing every cell possible to the right of my range.

 

[rtirado]

Hello Footoo. Your code works perfect. Thank you so much. I would of never been able to figure that out within my time frame.