Activate Multiple Ranges
Andi,
Replace
Range(Var_Range).Select
with
If Application.CountIf(Sheet1.Range("A1", _
Sheet1.Cells(r, 1)), Var_Sheet) 1 Then
Union(Selection, Range(Var_Range)).Select
Else
Range(Var_Range).Select
End If
HTH,
Bernie
MS Excel MVP
"Andibevan" wrote in message
...
Hi All,
The following macro loops through sheet names and cell ranges that are
located on sheet 1 of my spreadsheet and selects each range. On Sheet 1
Column A contains worksheets name and column b contains the range in the
following format:-
sheet2 a1:b2
sheet3 c1:d1
sheet3 c3:d3
The macro works fine as long as there is only 1 range to select per
worksheet. How do I modify the code to be able to select multiple ranges
on
each worksheets (i.e. in the above data it would select both c1:d1 and
c3:d3
on sheet3).
Any pointers or advice would be greatfully received.
Ta
Andi
Sub try_select()
Dim Const_Range As Range
Dim Var_Sheet
Dim Var_Range
Start_r = 1 'Start Row
last_r = Sheet1.Range("A65536").End(xlUp).Row 'last row
const_raneg = Sheet1.Range("a" & Start_r & ":b" & last_r) 'Range
containing
information
r = Start_r
workcount = Worksheets.Count
last_sheet = Worksheets(workcount).Name
For Each Row In const_raneg
Sheet1.Activate
Var_Sheet = Cells(r, 1)
Var_Range = Cells(r, 2)
Worksheets(Var_Sheet).Activate
Range(Var_Range).Select
r = r + 1
If r = last_r + 2 - Start_r Then Exit For
Next Row
End Sub
|