Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming | |||
how to? set my range= my UDF argument (range vs. value in range) [advanced?] | Excel Programming |