Set Rng

[nigelk]

Hi all,

I need to set a range equal to every other row, but don't want to 'hard code' it.

Ie

Set Rng = Range("G3,G5,G7")

as the data changes in size. I can get the size of data by using .End(xlup).

Is it possible to set it automatically?

Thanks,Nigel

 

[VoG]

I can't think of a clever way. How about

Dim Rng As Range, LR As Long, i As Long
LR = Range("G" & Rows.Count).End(xlUp).Row
Set Rng = Range("G3")
For i = 5 To LR Step 2
    Set Rng = Union(Rng, Range("G" & i))
Next i

 

[Colin Legg]

Hi Nigel,

A simple way to do this is to use a loop, for example:

Sub foo()
    Dim rng As Range
    
    Dim lStart As Long
    Dim lFinish As Long
    Dim lCounter As Long
    
    lStart = 3
    lFinish = 15
    
    For lCounter = lStart To lFinish Step 2
        If rng Is Nothing Then
            Set rng = Cells(lCounter, "g")
        Else
            Set rng = Union(rng, Cells(lCounter, "g"))
        End If
    Next lCounter
    
    MsgBox rng.Address
    
End Sub

 

[nigelk]

Thanks Peter, that'll do me just fine.

Nigel.

 

[nigelk]

Thanks Colin.

 

[repairman615]

Hello All,

Colin, when I tried the code you provided. Seemed to go further than the test range in G3:G7.

Maybe:

For lCounter = lStart To lFinish / 2 Step 2

Jeff

 

[shg]

I have a function for that:

Function DisjointRange(cell1 As Range, cell2 As Range, nCell As Long) As Range
    ' shg 2009, 2010
    ' Returns an nCell range starting with cell1 and cell2, and the balance of
    ' cells having the same spacing
 
    Dim i           As Long
    Dim iy          As Long
    Dim ix          As Long
 
    If cell1.Cells.Count * cell2.Cells.Count > 1 Then Exit Function
    If nCell < 2 Then Exit Function
    If Not cell1.Worksheet Is cell2.Worksheet Then Exit Function
 
    Set DisjointRange = Union(cell1, cell2)
    ix = cell2.Column - cell1.Column
    iy = cell2.Row - cell1.Row
    On Error GoTo Oops
 
    For i = 2 To nCell - 1
        Set DisjointRange = Union(DisjointRange, cell1.Offset(i * iy, i * ix))
    Next i
    Exit Function
 
Oops:
    Err.Clear
    Set DisjointRange = Nothing
End Function

E.g., you can sum the cells on main diagonal of A1:E5:

=SUM(DisjointRange(A1, B2, 5))

 

[Colin Legg]

Hi Jeff,

Colin, when I tried the code you provided. Seemed to go further than the test range in G3:G7.

In the example I gave, I used 15 as the last row so it would loop through to 15, not 7. nigelk said that the last row was going to be dynamically set, but I just used 15 for simplicity. Hope that clears up any confusion!

 

[CPerdue]

Hello Gents,

I tried Colin's example, which worked, then I tried to add data to the cells...

Sub foo()
    Dim rng As Range
 
    Dim lStart As Long
    Dim lFinish As Long
    Dim lCounter As Long
 
    lStart = 3
    lFinish = 15
 
    For lCounter = lStart To lFinish Step 2
        If rng Is Nothing Then
            Set rng = Cells(lCounter, "g")
        Else
            Set rng = Union(rng, Cells(lCounter, "g"))
        End If
    Next lCounter
 
'try to write in data...
    Dim icount As Integer
 
    For icount = 1 To 5
        Range("rng").Cells(icount).Value = icount
    Next icount
 
    MsgBox rng.Address
End Sub

I get runtime error 1004, not very helpful.

At the danger of a thread hijack, what is the *right* way to address data in discontinuous ranges?

I'm trying for a discontinuous range (for formatting) and I'd like to get at the data by the Range("name")(index) syntax, but the index seems to ignore the discontinuity.

Sub scratch()
 
    Dim myRangeA As Range
 
    Set myRangeA = Range("$A$1:$A$5,$A$11:$A$15")
 
    For icount = 1 To 10
        myRangeA(icount) = icount
    Next icount
 
End Sub

will put digits 1-10 in A1:A10 instead of in A1:A5, skip five rows, and continuing in A11:15.

It is looking like another long weekend...

thanks,

Clint

 

[RoryA]

You would have to use For each cell in range("blah").Cells instead, or if you need to use counters, you need to loop the areas in the range and then the cells in each area.