Need help setting a range
 

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!

Need help setting a range
 

Hi,

Try this (Untested!)

Sub SetRange()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("a1:a" & lastrow)
For col = 3 To 55 Step 2
Set rng = Union(rng, Range(Cells(1, col), Cells(lastrow, col)))
Next col

End Sub

HTH

"GettingThere" wrote:

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!

Need help setting a range
 

Do you mean setting a range object?

lstRow = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A2:A" & lstRow)

if you mean select it, then

Range("A2:A" & lstRow).Select

--

HTH

RP
(remove nothere from the email address if mailing direct)


"GettingThere" wrote in message
...
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!

Need help setting a range
 

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" wrote in message
...
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!

Need help setting a range
 

Thanks Toppers - it works perfectly!

"Toppers" wrote:

Hi,

Try this (Untested!)

Sub SetRange()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("a1:a" & lastrow)
For col = 3 To 55 Step 2
Set rng = Union(rng, Range(Cells(1, col), Cells(lastrow, col)))
Next col

End Sub

HTH

"GettingThere" wrote:

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!

Need help setting a range
 

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" wrote in message
...
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!

Need help setting a range
 

Hi GettingTheree,

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

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" wrote in message
...
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" wrote in message
...
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!

Need help setting a range
 

Hi Norman,

Gosh, I am such a loser! When I went to post your amended code in to a
module, I noticed that I had managed to change on line of your previous to
"For Each col In Columns("A:A")". That explains a lot!

(Don't ask...I don't remember changing it, don't know why I would have. Yet
there it is, staring me right in the face)

It's ironic that you set the interior color index in your second example, as
that is exactly the reason I wanted to set the range!

Thanks for the help!





"Norman Jones" wrote:

Hi GettingTheree,

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

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" wrote in message
...
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" wrote in message
...
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!