Range data type, Selecting a column (2000)

[KevinUCD]

Hello all,

I need to run a loop on only the populated cells in a column of my sheet. I can obtain the last occupied row using this code:

<pre> Set oLastRow = Range("A65536").End(xlUp)

lLastRow = Range("A65536").End(xlUp).Row
lLastRow = lLastRow + 1
</pre>


I can select the first column in this last row using:

<pre> Range("A" & lLastRow).Select
</pre>


and I could store this in some variable called MyLastRow and another variable I can use to store the first row.

<pre> MyLastRow = "A" & lLastRow
MyFirstRow = "A1"
</pre>


So I have enough information to select the first column. I tried

<pre>Range(MyFirstRow:MyFirstRow)
</pre>


But it doesn't work. How can I use my variables (Strings) in a Range(X:Y) statement?

[JohnBF]

I think the syntax uses a comma, not a colon:

range(startcell, endcell)

and you can drop the locations directly into the range object like this:

Cells(Application.Rows.Count, Range("RangeName").Column).End(xlUp).Select
Range(ActiveCell, ActiveCell.Offset(-ActiveCell.Row + 1)).Name = "RangeName"

( I wrote this a few days ago so it would include any blank cells between end and start, which looks to be similar to your objective.)

[notbrl]

just a quickie but try something like this:

<pre>Sub test()
Dim MyFirstRow, MyLastRow, lLastRow
Set lLastRow = Range("A65536").End(xlUp)
lLastRow = Range("A65536").End(xlUp).Row
lLastRow = lLastRow + 1


MyFirstRow = "A1"
MyLastRow = "A" & lLastRow


Range("A" & lLastRow).Select
Range(MyFirstRow, MyLastRow).Select
End Sub
</pre>

[JohnBF]

OK, I'm wrong, the syntax also takes a single argument,

range("a1:b1")

and Andrew has pointed out the problem, but in any case I hope my response was of use!

[Andrew Cronnolly]

Kevin,

Try <pre> Range(MyFirstRow & ":" & "A" & lLastRow)</pre>

Edit: To include & "A"

Andrew C

[KevinUCD]

Thanks guys!

[SammyB]

<pre>ActiveSheet.UsedRange.Columns(1)</pre>

is easier.

[JohnBF]

That is handy, but if RangeName top and bottom cells are A1 to A143, and Activesheet.UsedRange is A1 to G450, the returned range from
ActiveSheet.UsedRange.Columns(Range("RangeName").C olumn).Select
is A1 to A450. I messed around a while and couldn't find a construct that would solve this problem; any ideas?

[SammyB]

John, I just wanted to point out that if you have a range, then you can use Columns to pick out individual "columns" that only include cells from the original range. For example, if I know that I have a bunch on non-empty cells around A1, then to iterate through the ones in column A, I use<pre> For Each c In Range("A1").CurrentRegion.Columns(1)</pre>

You still need to check for empty cells, but it gets the iteration down to manageable proportions. --Sam