Re: Need help setting a range

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi GettingTheree,

Unlike you, I could not produce a scenario in which the procedure would not
work, even using an empty unused work***.

As written, the procedure does nothing except to set a range.

Try again with the addition of two final diagnostic lines:

Public Sub Tester11()
Dim rng As Range
Dim i As Long
Dim col As Range

i = Cells(Rows.Count, "A").End(xlUp).Row


For Each col In Columns("A:BC")

If col.Column Mod 2 = 1 Then

If Not rng Is Nothing Then
Set rng = Union(rng, col.Cells(1).Resize(i))
Else
Set rng = col.Cells(1).Resize(i)
End If
End If

Next col

MsgBox rng.Parent.Parent.Name & "(" _
& rng.Parent.Name & ")" _
& vbNewLine & rng.Address

'Or, on the basis that a picture is worth a thousand words:
rng.Interior.ColorIndex = 6

End Sub
'<======================

---
Regards,
Norman



"GettingThere" <GettingThere@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0036115C-31C1-44A8-88EC-DE4B8D66848C@xxxxxxxxxxxxxxxx
> Hi Norman,
>
> For some reason, this always sets the range to the used range in column A.
> Any ideas?
>
> Thanks much.
>
> "Norman Jones" wrote:
>
>> Hi GettingThere,
>>
>> Try:
>>
>> '=========================>>
>> Public Sub Tester11()
>> Dim rng As Range
>> Dim i As Long
>> Dim col As Range
>>
>> i = Cells(Rows.Count, "A").End(xlUp).Row
>>
>>
>> For Each col In Columns("A:BC")
>>
>> If col.Column Mod 2 = 1 Then
>>
>> If Not rng Is Nothing Then
>> Set rng = Union(rng, col.Cells(1).Resize(i))
>> Else
>> Set rng = col.Cells(1).Resize(i)
>> End If
>> End If
>>
>> Next col
>>
>> End Sub
>> '<,======================
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "GettingThere" <GettingThere@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:DDB8F0DF-28E1-4C0E-99C8-C5B4C4576E9C@xxxxxxxxxxxxxxxx
>> > I'm trying to set a range where the row range is:
>> >
>> > "A2:A" & lstRow ' where lstRow = Range("A" & Rows.Count).End(xlUp).Row
>> >
>> > and the columns range is the odd numbered columns in A:BC.
>> >
>> > Do I need to use Intersect? I'm stumped!
>> >
>> > Thanks in advance!
>>
>>
>>


.

Quantcast