Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Last non blank row in a worksheet
Hello,
Whenevr I try to get the number of rows in a worksheet, it returns 65526 which is actually the maximum number of possible in a sheet. If I have used only 7 rows in a sheet, I want to get the answer as 7. How can I get? Thank you |
#2
|
|||
|
|||
To use a worksheet function, if you have a column that never contains embedded
blank cells, you can write something like =COUNTA(A:A). Or, if you know which column will be the longest, say C, you can use an array formula something like this =MAX(IF(ISBLANK(C1:C2000),0,ROW(C1:C2000))) Enter this with CTRL+SHIFT+ENTER. Change the number 2000, if necessary, to some number that you know is too high. On Sun, 30 Jan 2005 20:07:31 -0800, "gana" wrote: Hello, Whenevr I try to get the number of rows in a worksheet, it returns 65526 which is actually the maximum number of possible in a sheet. If I have used only 7 rows in a sheet, I want to get the answer as 7. How can I get? Thank you |
#3
|
|||
|
|||
"Myrna Larson" wrote...
To use a worksheet function, if you have a column that never contains embedded blank cells, you can write something like =COUNTA(A:A). Or, if you know which column will be the longest, say C, you can use an array formula something like this =MAX(IF(ISBLANK(C1:C2000),0,ROW(C1:C2000))) Enter this with CTRL+SHIFT+ENTER. Change the number 2000, if necessary, to some number that you know is too high. .... More efficient to use the array formula =MATCH(2,1/(1-ISBLANK(C1:C2000))) |
#4
|
|||
|
|||
Hello,
Thanks for the reply. But I'm trying to do this in excel automation. Whenever I use the property count..it returns the maximum number of rows. How to get the actual number of rows usd in that perticular sheet. Thank you. "gana" wrote: Hello, Whenevr I try to get the number of rows in a worksheet, it returns 65526 which is actually the maximum number of possible in a sheet. If I have used only 7 rows in a sheet, I want to get the answer as 7. How can I get? Thank you |
#5
|
|||
|
|||
Hi Harlan, how about the following with no need to array enter I think?
=LOOKUP(2,1/(C1:C2000<""),ROW(C1:C2000)) Are there any instances where the MATCH function would catch it that the LOOKUP wouldn't. I've only just started playing with this use of the function based on some posts of Aladin's in Mr Excel so just curious. Regards Ken............. "Harlan Grove" wrote: "Myrna Larson" wrote... To use a worksheet function, if you have a column that never contains embedded blank cells, you can write something like =COUNTA(A:A). Or, if you know which column will be the longest, say C, you can use an array formula something like this =MAX(IF(ISBLANK(C1:C2000),0,ROW(C1:C2000))) Enter this with CTRL+SHIFT+ENTER. Change the number 2000, if necessary, to some number that you know is too high. .... More efficient to use the array formula =MATCH(2,1/(1-ISBLANK(C1:C2000))) |
#6
|
|||
|
|||
LastRw = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
LastRw = Sht1.Cells(Rows.Count, "A").End(xlUp).Row LastRw = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count First 2 based on a particular column, and last one based on usedrange. Regards Ken................ "gana" wrote: Hello, Thanks for the reply. But I'm trying to do this in excel automation. Whenever I use the property count..it returns the maximum number of rows. How to get the actual number of rows usd in that perticular sheet. Thank you. "gana" wrote: Hello, Whenevr I try to get the number of rows in a worksheet, it returns 65526 which is actually the maximum number of possible in a sheet. If I have used only 7 rows in a sheet, I want to get the answer as 7. How can I get? Thank you |
#7
|
|||
|
|||
Ken Wright wrote...
Hi Harlan, how about the following with no need to array enter I think? =LOOKUP(2,1/(C1:C2000<""),ROW(C1:C2000)) Are there any instances where the MATCH function would catch it that the LOOKUP wouldn't. I've only just started playing with this use of the function based on some posts of Aladin's in Mr Excel so just curious. .... "Harlan Grove" wrote: .... =MATCH(2,1/(1-ISBLANK(C1:C2000))) Aside from the brevity of the MATCH formula, it's processing a single array, whereas the LOOKUP formula requires two derived arrays. That can't improve recalc speed. Then there's the all too common equivalence drawn between a cell evaluation to "" and being blank. If blank or pseudoblank ("") should be considered equivalent, then =MATCH(2,1/(C1:C2000<"")) Otherwise, ISBLANK is necessary. While I try to avoid array formulas whenever I can, there are times when they provide better solutions. IMO, this is one of those cases. |
#8
|
|||
|
|||
Cheers Harlan, much appreciated.
-- Regards Ken....................... "Harlan Grove" wrote in message oups.com... Ken Wright wrote... Hi Harlan, how about the following with no need to array enter I think? =LOOKUP(2,1/(C1:C2000<""),ROW(C1:C2000)) Are there any instances where the MATCH function would catch it that the LOOKUP wouldn't. I've only just started playing with this use of the function based on some posts of Aladin's in Mr Excel so just curious. ... "Harlan Grove" wrote: ... =MATCH(2,1/(1-ISBLANK(C1:C2000))) Aside from the brevity of the MATCH formula, it's processing a single array, whereas the LOOKUP formula requires two derived arrays. That can't improve recalc speed. Then there's the all too common equivalence drawn between a cell evaluation to "" and being blank. If blank or pseudoblank ("") should be considered equivalent, then =MATCH(2,1/(C1:C2000<"")) Otherwise, ISBLANK is necessary. While I try to avoid array formulas whenever I can, there are times when they provide better solutions. IMO, this is one of those cases. |
#9
|
|||
|
|||
Which may or may not be correct, depending upon what Excel thinks is the "used
range". I would make sure the used range had been reset before depending upon Excel's estimation. Or use this UDF in combination with a macro. Function RangeToUse(anySheet As Worksheet) As Range Dim i As Integer, C As Integer, R As Integer With anySheet.UsedRange i = .Cells(.Cells.Count).Column + 1 For C = i To 1 Step -1 If Application.CountA(anySheet.Columns(C)) 0 _ Then Exit For Next i = .Cells(.Cells.Count).Row + 1 For R = i To 1 Step -1 If Application.CountA(anySheet.Rows(R)) 0 Then _ Exit For Next End With With anySheet Set RangeToUse = .Range(.Cells(1, 1), .Cells(R, C)) End With End Function Sub UsedRangePick() Dim tempRange As Range Set tempRange = RangeToUse(ActiveSheet) tempRange.Select mycount = Selection.Rows.Count MsgBox "This selection contains " & mycount _ & " row(s)", vbInformation, "Count Rows" End Sub Gord Dibben Excel MVP On Mon, 31 Jan 2005 04:47:06 -0800, "Ken Wright" wrote: LastRw = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count First 2 based on a particular column, and last one based on usedrange. |
#10
|
|||
|
|||
Gord Dibben wrote...
.... Or use this UDF in combination with a macro. Function RangeToUse(anySheet As Worksheet) As Range Dim i As Integer, C As Integer, R As Integer With anySheet.UsedRange i = .Cells(.Cells.Count).Column + 1 For C = i To 1 Step -1 If Application.CountA(anySheet.Columns(C)) 0 Then Exit For Next i = .Cells(.Cells.Count).Row + 1 For R = i To 1 Step -1 If Application.CountA(anySheet.Rows(R)) 0 Then Exit For Next End With With anySheet Set RangeToUse = .Range(.Cells(1, 1), .Cells(R, C)) End With End Function .... Repeatedly calling COUNTA on worksheet ranges isn't a recipe for nimble VBA procedures. Also, if the goal were locating the last row with nonblank cells, there's no reason to reduce the number of columns. Finally, this might be useful as a UDF, so generalize it. Function bmr(Optional x As Variant) As Long Dim ur As Range, r As Range If IsMissing(x) Then Set ur = ActiveSheet.UsedRange ElseIf TypeOf x Is Worksheet Then Set ur = x.UsedRange ElseIf TypeOf x Is Range Then Set ur = x.Worksheet.UsedRange Else On Error Resume Next bmr = -1 'return -1 for invalid args Set ur = Evaluate(x).Worksheet.UsedRange If Err.Number < 0 Then Exit Function On Error GoTo 0 End If Set r = ur.Cells(ur.Cells.Count) Do While r.Row 1 And r.Formula = "" And r.End(xlToLeft).Formula = "" Set r = r.Offset(-1, 0) Loop 'return 0 if used range is A1 only and A1 is blank If r.Row 1 Or r.Formula < "" Or r.End(xlToLeft).Formula < "" Then _ bmr = r.Row End Function And similarly for the rightmost column. Function rmc(Optional x As Variant) As Long Dim ur As Range, r As Range If IsMissing(x) Then Set ur = ActiveSheet.UsedRange ElseIf TypeOf x Is Worksheet Then Set ur = x.UsedRange ElseIf TypeOf x Is Range Then Set ur = x.Worksheet.UsedRange Else On Error Resume Next rmc = -1 'return -1 for invalid args Set ur = Evaluate(x).Worksheet.UsedRange If Err.Number < 0 Then Exit Function On Error GoTo 0 End If Set r = ur.Cells(ur.Cells.Count) Do While r.Column 1 And r.Formula = "" And r.End(xlUp).Formula = "" Set r = r.Offset(0, -1) Loop 'return 0 if used range is A1 only and A1 is blank If r.Column 1 Or r.Formula < "" Or r.End(xlUp).Formula < "" Then _ rmc = r.Column End Function And generate the range from A1 to the last cell in which there's a nonblank cell in the same row and column using the previous functions. Function nbr(Optional x As Variant) As Range Dim ws As Worksheet Dim r As Long, c As Long If IsMissing(x) Then Set ws = ActiveSheet ElseIf TypeOf x Is Worksheet Then Set ws = x ElseIf TypeOf x Is Range Then Set ws = x.Worksheet Else On Error Resume Next 'return Nothing for invalid args Set ws = Evaluate(x).Worksheet If Err.Number < 0 Then Exit Function On Error GoTo 0 End If r = bmr(ws) c = rmc(ws) If r 0 And c 0 Then Set nbr = ws.Range("A1").Resize(r, c) Else Set nbr = ws.Range("A1") End If End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generating a blank worksheet at the start of the month | New Users to Excel | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions | |||
Reference Data in Moved Worksheet | Setting up and Configuration of Excel | |||
my worksheet getting blank | Excel Discussion (Misc queries) |