Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding max row containing data...
Hi -
Does anyone know how to quickly find the maximum row in an Excel spreadsheet containing data? I have data between columns A-W randomly (with holes) to some unknown row, and I need to identify which row that is. Some sheets may have that 'maximum row' data data in column A, another it might be in column K, etc. Thanks very much for any help! Dan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding max row containing data...
Hi!
One way: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =MAX((A1:W1500<"")*(ROW(A1:W1500))) Use a large enough range to cover the expected result. Biff "Dan" wrote in message ... Hi - Does anyone know how to quickly find the maximum row in an Excel spreadsheet containing data? I have data between columns A-W randomly (with holes) to some unknown row, and I need to identify which row that is. Some sheets may have that 'maximum row' data data in column A, another it might be in column K, etc. Thanks very much for any help! Dan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding max row containing data...
Thanks, Biff. Can this be used in VBA? I tried it and am not sure how it
works. What do you mean by "Entered as an array using the key combo of CTRL,SHIFT,ENTER"? Thanks! Dan "Biff" wrote: Hi! One way: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =MAX((A1:W1500<"")*(ROW(A1:W1500))) Use a large enough range to cover the expected result. Biff "Dan" wrote in message ... Hi - Does anyone know how to quickly find the maximum row in an Excel spreadsheet containing data? I have data between columns A-W randomly (with holes) to some unknown row, and I need to identify which row that is. Some sheets may have that 'maximum row' data data in column A, another it might be in column K, etc. Thanks very much for any help! Dan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding max row containing data...
Hi!
Can this be used in VBA? That is a worksheet formula. VBA would be an entirely different technique. I can't help with VBA. What do you mean by "Entered as an array using the key combo of CTRL,SHIFT,ENTER"? Type the formula, then, instead of just hitting the ENTER key hold down the CTRL key and the SHIFT key then hit ENTER. When done properly Excel will place squiggly braces { } around the formula. You cannot just type the braces in, you must use the key combination. Biff "Dan" wrote in message ... Thanks, Biff. Can this be used in VBA? I tried it and am not sure how it works. What do you mean by "Entered as an array using the key combo of CTRL,SHIFT,ENTER"? Thanks! Dan "Biff" wrote: Hi! One way: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =MAX((A1:W1500<"")*(ROW(A1:W1500))) Use a large enough range to cover the expected result. Biff "Dan" wrote in message ... Hi - Does anyone know how to quickly find the maximum row in an Excel spreadsheet containing data? I have data between columns A-W randomly (with holes) to some unknown row, and I need to identify which row that is. Some sheets may have that 'maximum row' data data in column A, another it might be in column K, etc. Thanks very much for any help! Dan |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding max row containing data...
Dan
VBA.......... Sub LastRow() Dim myLastRow As Long With ActiveSheet On Error Resume Next myLastRow = _ .Cells.Find("*", after:=.Cells(1), _ LookIn:=xlFormulas, lookat:=xlWhole, _ searchdirection:=xlPrevious, _ searchorder:=xlByRows).Row End With MsgBox "last row is " & myLastRow End Sub Formula............... Biff's formula will be entered in a cell not in the range A1:W1500 Copy or type it into a cell then CTRL + SHIFT and hit ENTER. This will enter it as an array formula and place curly brackets around it. {=MAX((A1:W1500<"")*(ROW(A1:W1500)))} Gord Dibben Excel MVP On Sat, 26 Nov 2005 11:17:03 -0800, Dan wrote: Thanks, Biff. Can this be used in VBA? I tried it and am not sure how it works. What do you mean by "Entered as an array using the key combo of CTRL,SHIFT,ENTER"? Thanks! Dan "Biff" wrote: Hi! One way: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =MAX((A1:W1500<"")*(ROW(A1:W1500))) Use a large enough range to cover the expected result. Biff "Dan" wrote in message ... Hi - Does anyone know how to quickly find the maximum row in an Excel spreadsheet containing data? I have data between columns A-W randomly (with holes) to some unknown row, and I need to identify which row that is. Some sheets may have that 'maximum row' data data in column A, another it might be in column K, etc. Thanks very much for any help! Dan |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding max row containing data...
Thanks a lot! It worked great - it was exactly what I was looking for.
Dan "Gord Dibben" wrote: Dan VBA.......... Sub LastRow() Dim myLastRow As Long With ActiveSheet On Error Resume Next myLastRow = _ .Cells.Find("*", after:=.Cells(1), _ LookIn:=xlFormulas, lookat:=xlWhole, _ searchdirection:=xlPrevious, _ searchorder:=xlByRows).Row End With MsgBox "last row is " & myLastRow End Sub Formula............... Biff's formula will be entered in a cell not in the range A1:W1500 Copy or type it into a cell then CTRL + SHIFT and hit ENTER. This will enter it as an array formula and place curly brackets around it. {=MAX((A1:W1500<"")*(ROW(A1:W1500)))} Gord Dibben Excel MVP On Sat, 26 Nov 2005 11:17:03 -0800, Dan wrote: Thanks, Biff. Can this be used in VBA? I tried it and am not sure how it works. What do you mean by "Entered as an array using the key combo of CTRL,SHIFT,ENTER"? Thanks! Dan "Biff" wrote: Hi! One way: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =MAX((A1:W1500<"")*(ROW(A1:W1500))) Use a large enough range to cover the expected result. Biff "Dan" wrote in message ... Hi - Does anyone know how to quickly find the maximum row in an Excel spreadsheet containing data? I have data between columns A-W randomly (with holes) to some unknown row, and I need to identify which row that is. Some sheets may have that 'maximum row' data data in column A, another it might be in column K, etc. Thanks very much for any help! Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding common Data in multiple worksheets | Excel Discussion (Misc queries) | |||
Charts not recognizing source data if original linked data is changed. | Charts and Charting in Excel | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Finding min/max of adjacent data in a range of cells | Excel Worksheet Functions | |||
Finding, and returning data. | New Users to Excel |