I apologize if I didn't state the subject accurately.
I have numbers scattered across a spreadsheet. I have code to get the ranges of all those cells of a specific value into one range object via the union function. I want to select those same ranges but on a different worksheet. This is so I can have a color map of all the values but on a different sheet. Thank you very much for your time.
I have numbers scattered across a spreadsheet. I have code to get the ranges of all those cells of a specific value into one range object via the union function. I want to select those same ranges but on a different worksheet. This is so I can have a color map of all the values but on a different sheet. Thank you very much for your time.
VBA Code:
Dim cell As Range
Dim myRng As Range
For Each cell In ActiveSheet.Range("A1:M56")
If cell.Value = 193 Then
If Not myRng Is Nothing Then
Set myRng = Union(myRng, cell)
Else
Set myRng = cell
End If
End If
Next cell
'alt 1----------------------------
myRng.select 'This selects all the cells with the value 193 but I want to select those ranges on the "GRID" worksheet, not the active one with all the values in it.
'alt 2----------------------------
Sheets("GRID").Select
myRng.select 'This doesn't work. I assume it's because myRng is storing the original sheet in it. Is there a way I can just get the range without the parent?
'alt 3----------------------------
Sheets("GRID").Range(myRng.Address).Select 'This works like I generally want it to, but I hit a character limit of 253 and the selection doesn't include the full range when I have a larger dataset.
'I printed myRng.Address and got:
'--"$A$1:$M$3,$B$4:$M$4,$C$5:$M$5,$D$6:$M$6,$E$7:$M$7,$F$8:$M$8,$G$9:$M$9,$H$10:$M$10,$I$11:$M$11,$J$12:$M$12,$K$13:$M$13,$L$14:$M$14,$M$15,$L$16,$K$15:$K$17,$M$17,$J$14:$J$18,$L$18:$M$18,$E$9:$E$19,$I$13:$I$19,$K$19:$M$19,$D$8:$D$20,$F$10:$F$20,$G$11:$G$20"
'I am limited to 253 as my character limit for holding a range address. For my purposes I need that to be able to hold far much more than that.
'I've tried breaking that long address into an array with the comma as a delimiter but that was getting too convoluted. I feel like I am missing something simple.
End Sub