We help IT Professionals succeed at work.

VB Selecting non-adjacent range.

carlosab
carlosab asked
on
I  need some script to select a range of non-adjacent cells based on the current selection.

Here is a sample of the data:

 Sample
In this sample, if the user selects cell A5, A6, A7, or A8 and then runs the script, then the relevant rows would be 5:8 since they all have the same value in Column A. (The data is sorted on column A). Then, within those relevant rows, columns C and D are always selected. So, here, Cells C5:C8 would be selected.
Comment
Watch Question

Author

Commented:
Sorry. My sample should have ended with "so, here, Cells C5:D8 would be selected."
Try this code

Sub selectsj()
Dim rw1 As Range
Dim rwl As Range
Set rw1 = ActiveSheet.Range("A:A").Find(Selection.Value)
Set rwl = ActiveSheet.Range("A:A").Find(Selection.Value, , , , , xlPrevious)
Union(ActiveSheet.Range(rw1.Address & ":" & rwl.Address), ActiveSheet.Range(rw1.Address & ":" & rwl.Address).Offset(0, 2).Resize(, 2)).Select
End Sub

Author

Commented:
Works nice, except that it is also selecting A5:A8. Nothing in A should be selected. Only c5:D8.
Change

Union(ActiveSheet.Range(rw1.Address & ":" & rwl.Address), ActiveSheet.Range(rw1.Address & ":" & rwl.Address).Offset(0, 2).Resize(, 2)).Select

to

ActiveSheet.Range(rw1.Address & ":" & rwl.Address).Offset(0, 2).Resize(, 2).Select

Author

Commented:
Thanks.