Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last line in macro
How do I code a macro (or keystroke it) to find the last row that has data in
a column without harcoding the macro to a specific row? I have sections of data (every 6th row is blank) and I want to be able to find the absolute last row that has data in it for column B no matter where that last row is. Thanx. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last line in macro
Insert the following near the beging of your code:
Range("B65536").Select Selection.End(xlUp).Select If you want the row number: r = ActiveCell.Row "GJR3599" wrote: How do I code a macro (or keystroke it) to find the last row that has data in a column without harcoding the macro to a specific row? I have sections of data (every 6th row is blank) and I want to be able to find the absolute last row that has data in it for column B no matter where that last row is. Thanx. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last line in macro
Glenn,
WHY the selects? Is there a need to write slow code on purpose.? r= Activesheet.Cells(rows.count,2).end(xlup).row -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Glenn Ray wrote : Insert the following near the beging of your code: Range("B65536").Select Selection.End(xlUp).Select If you want the row number: r = ActiveCell.Row "GJR3599" wrote: How do I code a macro (or keystroke it) to find the last row that has data in a column without harcoding the macro to a specific row? I have sections of data (every 6th row is blank) and I want to be able to find the absolute last row that has data in it for column B no matter where that last row is. Thanx. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last line in macro
Not trying to be slow; just trying to show one way of getting the row.
Here's less code by 17 characters: r = Range("B65536").End(xlUp).Row "keepITcool" wrote: Glenn, WHY the selects? Is there a need to write slow code on purpose.? r= Activesheet.Cells(rows.count,2).end(xlup).row -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Glenn Ray wrote : Insert the following near the beging of your code: Range("B65536").Select Selection.End(xlUp).Select If you want the row number: r = ActiveCell.Row "GJR3599" wrote: How do I code a macro (or keystroke it) to find the last row that has data in a column without harcoding the macro to a specific row? I have sections of data (every 6th row is blank) and I want to be able to find the absolute last row that has data in it for column B no matter where that last row is. Thanx. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last line in macro
Glenn you miss the point:
17 characters less in a line of code dont necessarily mean it's faster. (although using the global Range object in this case is) my point was that the 2 SELECT statements in your original reply truely and unnecessarily slows down the code and necessitates screenupdating = false by using Activesheet it's clearer to OP which sheet is used. by using Rows.count it will work in future versions. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Glenn Ray wrote : Not trying to be slow; just trying to show one way of getting the row. Here's less code by 17 characters: r = Range("B65536").End(xlUp).Row "keepITcool" wrote: Glenn, WHY the selects? Is there a need to write slow code on purpose.? r= Activesheet.Cells(rows.count,2).end(xlup).row -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Glenn Ray wrote : Insert the following near the beging of your code: Range("B65536").Select Selection.End(xlUp).Select If you want the row number: r = ActiveCell.Row "GJR3599" wrote: How do I code a macro (or keystroke it) to find the last row that has data in a column without harcoding the macro to a specific row? I have sections of data (every 6th row is blank) and I want to be able to find the absolute last row that has data in it for column B no matter where that last row is. Thanx. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last line in macro
by using Rows.count it will work in future versions
Good point. Do you think MS would ever increase the number of rows and/or columns? If there are no performance drawbacks from that I wouldn't mind 1000.000 rows and say 1000 columns. RBS "keepITcool" wrote in message .com... Glenn you miss the point: 17 characters less in a line of code dont necessarily mean it's faster. (although using the global Range object in this case is) my point was that the 2 SELECT statements in your original reply truely and unnecessarily slows down the code and necessitates screenupdating = false by using Activesheet it's clearer to OP which sheet is used. by using Rows.count it will work in future versions. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Glenn Ray wrote : Not trying to be slow; just trying to show one way of getting the row. Here's less code by 17 characters: r = Range("B65536").End(xlUp).Row "keepITcool" wrote: Glenn, WHY the selects? Is there a need to write slow code on purpose.? r= Activesheet.Cells(rows.count,2).end(xlup).row -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Glenn Ray wrote : Insert the following near the beging of your code: Range("B65536").Select Selection.End(xlUp).Select If you want the row number: r = ActiveCell.Row "GJR3599" wrote: How do I code a macro (or keystroke it) to find the last row that has data in a column without harcoding the macro to a specific row? I have sections of data (every 6th row is blank) and I want to be able to find the absolute last row that has data in it for column B no matter where that last row is. Thanx. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last line in macro
apparently the spreadsheet object in webcomponents has lot's more rows... and what i hear the current attitude is to listen to the mvps. ofcourse the drawback is that some people will make sheets that will take 'a bit of time' to save and recalc. and ms has to sort out the usedrange muddle. copying some formats down a 1million row column.. just might upset the calculation engine. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam RB Smissaert wrote : by using Rows.count it will work in future versions Good point. Do you think MS would ever increase the number of rows and/or columns? If there are no performance drawbacks from that I wouldn't mind 1000.000 rows and say 1000 columns. RBS "keepITcool" wrote in message .com... Glenn you miss the point: 17 characters less in a line of code dont necessarily mean it's faster. (although using the global Range object in this case is) my point was that the 2 SELECT statements in your original reply truely and unnecessarily slows down the code and necessitates screenupdating = false by using Activesheet it's clearer to OP which sheet is used. by using Rows.count it will work in future versions. -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Glenn Ray wrote : Not trying to be slow; just trying to show one way of getting the row. Here's less code by 17 characters: r = Range("B65536").End(xlUp).Row "keepITcool" wrote: Glenn, WHY the selects? Is there a need to write slow code on purpose.? r= Activesheet.Cells(rows.count,2).end(xlup).row -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Glenn Ray wrote : Insert the following near the beging of your code: Range("B65536").Select Selection.End(xlUp).Select If you want the row number: r = ActiveCell.Row "GJR3599" wrote: How do I code a macro (or keystroke it) to find the last row that has data in a column without harcoding the macro to a specific row? I have sections of data (every 6th row is blank) and I want to be able to find the absolute last row that has data in it for column B no matter where that last row is. Thanx. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding last line in macro
I did some searching for information about the web component spreadsheet
control, but couldn't find much. I have situations where an array has to be displayed in the sheet, but has too many rows and/or columns. I have used Access for this now, but not everybody has Access. Also Access tables have a 255 field limit. RBS "keepITcool" wrote in message .com... apparently the spreadsheet object in webcomponents has lot's more rows... and what i hear the current attitude is to listen to the mvps. ofcourse the drawback is that some people will make sheets that will take 'a bit of time' to save and recalc. and ms has to sort out the usedrange muddle. copying some formats down a 1million row column.. just might upset the calculation engine. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam RB Smissaert wrote : by using Rows.count it will work in future versions Good point. Do you think MS would ever increase the number of rows and/or columns? If there are no performance drawbacks from that I wouldn't mind 1000.000 rows and say 1000 columns. RBS "keepITcool" wrote in message .com... Glenn you miss the point: 17 characters less in a line of code dont necessarily mean it's faster. (although using the global Range object in this case is) my point was that the 2 SELECT statements in your original reply truely and unnecessarily slows down the code and necessitates screenupdating = false by using Activesheet it's clearer to OP which sheet is used. by using Rows.count it will work in future versions. -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Glenn Ray wrote : Not trying to be slow; just trying to show one way of getting the row. Here's less code by 17 characters: r = Range("B65536").End(xlUp).Row "keepITcool" wrote: Glenn, WHY the selects? Is there a need to write slow code on purpose.? r= Activesheet.Cells(rows.count,2).end(xlup).row -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Glenn Ray wrote : Insert the following near the beging of your code: Range("B65536").Select Selection.End(xlUp).Select If you want the row number: r = ActiveCell.Row "GJR3599" wrote: How do I code a macro (or keystroke it) to find the last row that has data in a column without harcoding the macro to a specific row? I have sections of data (every 6th row is blank) and I want to be able to find the absolute last row that has data in it for column B no matter where that last row is. Thanx. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding top 80% line from a descending list | Excel Worksheet Functions | |||
Finding the last line on a spreadsheet | Excel Discussion (Misc queries) | |||
finding unknown from a line of best fit | Charts and Charting in Excel | |||
Macro problem on, Yellowed line - previous line or next line. | Excel Programming | |||
ListBox (finding Repost) (try to line up my example) | Excel Programming |