Maximum non-blank row in a specific range as a variable

[Chet]

Anyone know how to simply find the maximum non-blank row in a specific
range? I found this

=(MAX((DataRange2<>"")*ROW(DataRange2)))-ROW(DataRange2)+1

on Chip Pearson's web site but it's not exactly what I need. I
adapted it a bit because I need the result to be variable which I will
use in my VB code versus a static cell on the worksheet. What I tried
I have below but it doesn't work right yet.

MaxRow = FormulaArray((Application.WorksheetFunction.Max(("U5:AU25" <>
"") * Application.Rows("U5:AU25"))) - Rows("U5:AU25") + 1). It seems
like there should be a simple way (like this?) to do this.

Thanks for your help.
Chet

 

[Chet]

In case I didn't make it clear i'm trying to get the row number that
is the furthest down in the range for non-blank cells.

 

[JLGWhiz]

One way for a given range, If your active cell is inside the column range:

Sub lastRw()
x = ActiveCell.End(xlDown).Row
MsgBox x
End Sub

 

[Per Jessen]

In case I didn't make it clear i'm trying to get the row number that
is the furthest down in the range for non-blank cells.

Hi Chet

Try this:

LastRow = Range("U5").End(xlDown).Row

Regards,

Per

 

[JLGWhiz]

The MAX() worksheet function will produce the largest value in a range of
numerical values. It is not intended to produce a row number as such. Also,
if you mean the row count of non-blank cells then you would want CountIf() as
the function to sum up that number. I don't really understand what you want,
so I offer this in hopes that it will help you find the answer.

 

[Chet]

The MAX() worksheet function will produce the largest value in a range of
numerical values. It is not intended to produce a row number as such. Also,
if you mean the row count of non-blank cells then you would want CountIf() as
the function to sum up that number. I don't really understand what you want,
so I offer this in hopes that it will help you find the answer.







- Show quoted text -

What I have is data that is in the range U5:AU25 and for each column
the last non-blank row will vary. For example in column U the last
non-blank row might be 27, and V the last non-blank row might be 25, a
in W the last non-blank row might be 30. I need to return a variable
with the highest occupied non-blank row number. For my example the
variable would return a 30 since between U, V and W the highest row
number is 30. (Sorry for being unclear on that.) I do know how to
return the highest row number for a single column but thought there
might be a snazzy way to do that for my example of finding the highest
row number between a multiple column sample set.

Thanks,
Chet

 

[JLGWhiz]

OK try these. But if you use the UsedRange.Rows.Count, you might want to add
in a check of the cell above to make sure it is not empty because the used
range will pick up any type of data, even if it is invisible.

LastRow = Cells.Find _
("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

or

LastRow = ActiveSheet.UsedRange.Rows.Count

 

[Chet]

OK try these. But if you use the UsedRange.Rows.Count, you might want to add
in a check of the cell above to make sure it is not empty because the used
range will pick up any type of data, even if it is invisible.

LastRow = Cells.Find _
("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

or

LastRow = ActiveSheet.UsedRange.Rows.Count







- Show quoted text -

I'm surprised because I thought the UsedRange command applied to whole
activesheet where I am trying to find the highest non-blank row within
a specific range of cells. I don't think that what you are suggesting
will work. Respectfully...

Chet

 

[broro183]

hi Chet,

Does this work?
Dim LastRow As Long
LastRow = Application.WorksheetFunction.Max(Range("U5").End(xlDown).Row, _
Range("v5").End(xlDown).Row, Range("w5").End(xlDown).Row)

You could set your named ranges (eg DataRange2) to be dynamic & only extend
to the first blank row but the above is probably easier because you are
already using macros.

hth
Rob

__________________
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Per Jessen]

---------Cut -------

What I have is data that is in the range U5:AU25 and for each column
the last non-blank row will vary. For example in column U the last
non-blank row might be 27, and V the last non-blank row might be 25, a
in W the last non-blank row might be 30. I need to return a variable
with the highest occupied non-blank row number. For my example the
variable would return a 30 since between U, V and W the highest row
number is 30. (Sorry for being unclear on that.) I do know how to
return the highest row number for a single column but thought there
might be a snazzy way to do that for my example of finding the highest
row number between a multiple column sample set.

Thanks,
Chet

Hi Chet

This routine loop through columns U:UA and return the largest row number.

Option Explicit

Dim Target As Range
Dim c As Variant
Dim tRow As Long
Dim lRow As Long
Dim msg As String
Dim tColumn

Sub LastRow()
Set Target = Range("U5:UA25")
tColumn = Target.End(xlToLeft).Column
For Each c In Target.Columns
tRow = Cells(1, tColumn).End(xlDown).Row
If tRow > lRow Then lRow = tRow
tColumn = tColumn + 1
Next
msg = MsgBox("Last row = " & lRow)
End Sub

Regards

Per Jessen
DK

 

[Per Jessen]

---------Cut -------


Hi Chet

This routine loop through columns U:UA and return the largest row number.

Hi Chet

Just a little correction. Try this code instead:

Option Explicit

Dim Target As Range
Dim c As Variant
Dim tRow As Long
Dim lRow As Long
Dim msg As String
Dim tColumn as Long
Dim fRow As Long

Sub LastRow()
Set Target = Range("U5:UA25")
tColumn = Target.Column
fRow = Target.Row
For Each c In Target.Columns

tRow = Cells(fRow, tColumn).End(xlDown).Row
If tRow > lRow Then lRow = tRow
tColumn = tColumn + 1
Debug.Print tRow
Next
msg = MsgBox("Last row = " & lRow)
End Sub

 

[Chet]

Hi Chet

Just a little correction. Try this code instead:

Option Explicit

Dim Target As Range
Dim c As Variant
Dim tRow As Long
Dim lRow As Long
Dim msg As String
Dim tColumn as Long
Dim fRow As Long

Sub LastRow()
Set Target = Range("U5:UA25")
tColumn = Target.Column
fRow = Target.Row
For Each c In Target.Columns

tRow = Cells(fRow, tColumn).End(xlDown).Row
If tRow > lRow Then lRow = tRow
tColumn = tColumn + 1
Debug.Print tRow
Next
msg = MsgBox("Last row = " & lRow)
End Sub- Hide quoted text -

- Show quoted text -

Thanks much for trying. I was hoping for a one-liner piece of code to
do it with. I am able to code this myself also with a few lines of
code.
Best regards,
Chet