set range
 

Can someone please tell me how I can do the following?
I need a vb code to select last non-empty line of cells from Col A to Col Z.

E.g. I have data from A1:Z107, I need to select A107:z107

Thanks.

set range
 

Give this a try...

ActiveSheet.UsedRange.Select

HTH

"Jeff" wrote:

Can someone please tell me how I can do the following?
I need a vb code to select last non-empty line of cells from Col A to Col Z.

E.g. I have data from A1:Z107, I need to select A107:z107

Thanks.

set range
 

set rng = cells(rows.count,1).end(xlup)
do while application.CountA(rng.Resize(1,26)) < 0
set rng = rng.offset(1,0)
Loop
rng.resize(1,26).Select

--
Regards,
Tom Ogilvy

"Jeff" wrote in message
...
Can someone please tell me how I can do the following?
I need a vb code to select last non-empty line of cells from Col A to Col
Z.

E.g. I have data from A1:Z107, I need to select A107:z107

Thanks.

set range
 

Jim,
That selects everything. I am trying to select some columns only on
just the last row.
Even though i m trying to select the last row of colA to ColZ... there are
actually more than 26 columns containing data. I am not trying to select
everything (columns) on the last row.

Thanks,

Jeff


"Jim Thomlinson" wrote:

Give this a try...

ActiveSheet.UsedRange.Select

HTH

"Jeff" wrote:

Can someone please tell me how I can do the following?
I need a vb code to select last non-empty line of cells from Col A to Col Z.

E.g. I have data from A1:Z107, I need to select A107:z107

Thanks.

set range
 

Tom,
Sorry that I wasn't really specific. The spreadsheet that I have there
maybe empty spaces in the middle. So, the code you give here doesn't handle
that.
Also, From colA to ColZ they all end at the row (meaning that the absolute
row index for colA to Z will be the same). Is there a way to do that w/o a
loop?

spreadsheet looks sth like this.

row 1 Col A Col B ... Col Y Col Z
row 2
row 3 1 2 2 4
row 4 4 10 5 9

I'd like to select A4 thru Z4 in this case

Thanks



"Tom Ogilvy" wrote:

set rng = cells(rows.count,1).end(xlup)
do while application.CountA(rng.Resize(1,26)) < 0
set rng = rng.offset(1,0)
Loop
rng.resize(1,26).Select

--
Regards,
Tom Ogilvy

"Jeff" wrote in message
...
Can someone please tell me how I can do the following?
I need a vb code to select last non-empty line of cells from Col A to Col
Z.

E.g. I have data from A1:Z107, I need to select A107:z107

Thanks.

set range
 

Sorry about that. I misread what you wanted... How about this...

Range(ActiveSheet.Range("A65535").End(xlUp),
ActiveSheet.Range("A65535").End(xlUp).Offset(0, 26)).Select

HTH

"Jeff" wrote:

Jim,
That selects everything. I am trying to select some columns only on
just the last row.
Even though i m trying to select the last row of colA to ColZ... there are
actually more than 26 columns containing data. I am not trying to select
everything (columns) on the last row.

Thanks,

Jeff


"Jim Thomlinson" wrote:

Give this a try...

ActiveSheet.UsedRange.Select

HTH

"Jeff" wrote:

Can someone please tell me how I can do the following?
I need a vb code to select last non-empty line of cells from Col A to Col Z.

E.g. I have data from A1:Z107, I need to select A107:z107

Thanks.

set range
 

Sub BB()
Set rng = Cells(Rows.Count, 1).End(xlUp)
Do While Application.CountA(rng.Resize(1, 26)) < 0
Set rng = rng.Offset(1, 0)
Loop
rng.Offset(-1, 0).Resize(1, 26).Select
End Sub


would work with your test data. the only dependency is that column 1 has
data in it after any "all blank" rows within you data appear. I guess based
on your statements, you didn't test it. I did misread and selected the next
blank row at the bottom (after the last filled row), but the adjustment
would select the last filled row.

Here are some others

Most reliable regardless of data layout:
Sub AA()
Set rng = Cells.Find(What:="*", _
After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
Cells(rng.Row, 1).Resize(1, 26).Select
End Sub

less reliable because the usedrange can be overstated

set rng = ActiveSheet.UsedRange
rng.rows(rng.rows.count).Select


--
Regards,
Tom Ogilvy




"Jeff" wrote in message
...
Tom,
Sorry that I wasn't really specific. The spreadsheet that I have
there
maybe empty spaces in the middle. So, the code you give here doesn't
handle
that.
Also, From colA to ColZ they all end at the row (meaning that the absolute
row index for colA to Z will be the same). Is there a way to do that w/o
a
loop?

spreadsheet looks sth like this.

row 1 Col A Col B ... Col Y Col Z
row 2
row 3 1 2 2 4
row 4 4 10 5 9

I'd like to select A4 thru Z4 in this case

Thanks



"Tom Ogilvy" wrote:

set rng = cells(rows.count,1).end(xlup)
do while application.CountA(rng.Resize(1,26)) < 0
set rng = rng.offset(1,0)
Loop
rng.resize(1,26).Select

--
Regards,
Tom Ogilvy

"Jeff" wrote in message
...
Can someone please tell me how I can do the following?
I need a vb code to select last non-empty line of cells from Col A to
Col
Z.

E.g. I have data from A1:Z107, I need to select A107:z107

Thanks.

set range
 

Tom,
That only selected the first row. I want the last row.

Thanks,


"Jim Thomlinson" wrote:

Sorry about that. I misread what you wanted... How about this...

Range(ActiveSheet.Range("A65535").End(xlUp),
ActiveSheet.Range("A65535").End(xlUp).Offset(0, 26)).Select

HTH

"Jeff" wrote:

Jim,
That selects everything. I am trying to select some columns only on
just the last row.
Even though i m trying to select the last row of colA to ColZ... there are
actually more than 26 columns containing data. I am not trying to select
everything (columns) on the last row.

Thanks,

Jeff


"Jim Thomlinson" wrote:

Give this a try...

ActiveSheet.UsedRange.Select

HTH

"Jeff" wrote:

Can someone please tell me how I can do the following?
I need a vb code to select last non-empty line of cells from Col A to Col Z.

E.g. I have data from A1:Z107, I need to select A107:z107

Thanks.