Find last non-blank row with a worksheet function
Find the last row with a value.
Say I have a worksheet with a data entery range of A1 to A100. The user is required to enter data from row 1 downwards and cannot leave blanks between rows. I want to return the value in the last comleted row by function. Any ideas? For example that last row with a value may be in cell A20 of the A1:A100 range. Rgds, Bruce |
Find last non-blank row with a worksheet function
Hi!
Try one of these: If the data is all numeric: =LOOKUP(9.99999999999999E+307,A1:A100) If the data is all text: =LOOKUP(REPT("Z",255),A1:A100) If the data is mixed: =LOOKUP(2,1/(A1:A100<""),A1:A100) Biff "Bruce" wrote in message ... Find the last row with a value. Say I have a worksheet with a data entery range of A1 to A100. The user is required to enter data from row 1 downwards and cannot leave blanks between rows. I want to return the value in the last comleted row by function. Any ideas? For example that last row with a value may be in cell A20 of the A1:A100 range. Rgds, Bruce |
Find last non-blank row with a worksheet function
Perfect,
Thanks Biff. Bruce "Biff" wrote: Hi! Try one of these: If the data is all numeric: =LOOKUP(9.99999999999999E+307,A1:A100) If the data is all text: =LOOKUP(REPT("Z",255),A1:A100) If the data is mixed: =LOOKUP(2,1/(A1:A100<""),A1:A100) Biff "Bruce" wrote in message ... Find the last row with a value. Say I have a worksheet with a data entery range of A1 to A100. The user is required to enter data from row 1 downwards and cannot leave blanks between rows. I want to return the value in the last comleted row by function. Any ideas? For example that last row with a value may be in cell A20 of the A1:A100 range. Rgds, Bruce |
All times are GMT +1. The time now is 09:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com