Solved

# last row number in range

Posted on 2012-08-29
870 Views
Dear experts,

I have this vba code in excel where i can find the last filled row in a range.
This works perfectly when i start my range at A1 but when i start at A27 it returns the last row in range -26 because it starts counting then. i hope someone can help me.

``````Function FindRangeRowColumn(xlRange As Range, BeginLastcell As String) As String

Dim Firstrow As Long, lastRow As Long, firstCol As Long, LastCol As Long, strFirstCell As String, strLastCell As String

Dim lRow As Long, lCol As Integer, mRow As Long, mCol As Integer
lCol = xlRange.Columns.Count
mRow = 0
For i = 1 To lCol
lRow = Range(Cells(xlRange.Rows.Count, i), Cells(xlRange.Rows.Count, i)).End(xlUp).Row
If lRow > mRow Then
mRow = lRow
mCol = i
Else
End If
Next i

lastRow = mRow
LastCol = xlRange.Find(What:="*", After:=xlRange.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

strFirstCell = BeginLastcell

FindRangeRowColumn = strFirstCell + ":" + strLastCell

lastRow = mRow
end function
``````

Cheers,

Mark
0
Question by:MarkVrenken

LVL 8

Expert Comment

Not sure what your expected output is. What should it be based on the scenario below?

0

LVL 1

Author Comment

in this example it should be 27.

for example i have a range like A:22 to C:27. Then it checks every column for the last filled row. if the last row in A22:A27 = A24, the last row in B22:B27 is B:26, and the last row in C22:C27 is C25 I want to return 26.
What it does now i think is return 4 because:26-22 = 4

thanks for helping:)
0

LVL 13

Expert Comment

``````Option Explicit

Sub sample()
FindRangeRowColumn ("A2:D9")
End Sub
Function FindRangeRowColumn(xlString As String) As String

Dim iRow As Long, i As Long, lCol As Long, max As Long, colTrap As Long
Dim xlRange As Range

Set xlRange = Range(xlString)

lCol = xlRange.Columns.Count

max = 0
For i = 1 To lCol
iRow = ActiveSheet.Cells(Rows.Count, i).End(xlUp).Offset(0, 0).Row

If iRow >= max Then
max = iRow
colTrap = i
End If
Next i
End Function
``````
0

LVL 1

Author Comment

Thanks Shanan, but i don't think this will work. it still will return 8 when there is something in D9
0

LVL 1

Author Comment

my range is G27 to X27

i get 1 as iRow. what i want is 27.
I know this looks stupid because why would you want to get the last filled row if you only have range for 1 row. but that is only because i use this function several times.
0

LVL 13

Expert Comment

I am confused here.

You are saying you will get 1 as iRow means only 1 row is filled.

But you still want the 27 because the range has a 27? (X27)?
0

LVL 1

Author Comment

sorry to confuse you
My range will sometimes not start at A1 but for example A2:C4
If the last filled is C4 it returns 3 because my range is 3(A2,A3,A4) but i want to return 4

I want to seperate the columns and rows because sometimes C4 is empty while B4 is full Hope you understand me a bit.

Ultimately i want to select the range from the firstcell in my range: in this example A2, to my the last row and column(last row = 4) last column = C

now it returns C3 instead of C4
0

LVL 13

Assisted Solution

In the attached example,

My original function above returns B7

The reason being that the range is set at A2 (hence the 2nd row) so it is counting the filled rows from 2

If you want it to return B8 (and consider that the range is starting from A2) then the modified function would be

``````Sub sample()
FindRangeRowColumn ("A2:D9")
End Sub
Function FindRangeRowColumn(xlString As String) As String

Dim iRow As Long, i As Long, lCol As Long, max As Long, colTrap As Long
Dim xlRange As Range, lRow As Long

Set xlRange = Range(xlString)

lCol = xlRange.Columns.Count
lRow = xlRange.Cells(1, 1).Row - 1

max = 0
For i = 1 To lCol
iRow = ActiveSheet.Cells(Rows.Count, i).End(xlUp).Offset(lRow, 0).Row

If iRow >= max Then
max = iRow
colTrap = i
End If
Next i
End Function
``````
Temp.PNG
0

LVL 18

Accepted Solution

Hi

may be

``````Function FindRangeRowColumn(xlRange As Range) As Range

Dim x   As String
Dim n   As Long
Dim c   As Long

Set FindRangeRowColumn = Nothing

With xlRange
n = Evaluate("max(if(len(" & x & "),row(" & x & ")))")
c = Evaluate("max(if(len(" & x & "),column(" & x & ")))")
If n * c Then
Set FindRangeRowColumn = .Cells(1).Resize(n - .Row + 1, c - .Column + 1)
End If
End With

End Function
Sub kTest()
End Sub
``````

Kris
0

LVL 1

Author Comment

Hi kris,

thanks for your response, i think yours is more flexible but i don't really know what you are doing in your code. I guess this works like i want:). Thanks. Maybe you can explain to me how it really works, i understand just a little;)

@Shanan212, your code also works but is less flexible i guess. Thank you for your time.

i'm going to use kris' solution because i think it's more flexible. Thank you both for your effort and time.

Cheers,

Mark
0

LVL 18

Expert Comment

Hi Mark,

Thanks for the grade. It is a formula based approach. It finds the last row/column where the cell is not empty.

for e.g. you could try this in excel like

=max(if(len(a2:d9),row(a2:d9)))

it's an array formula.

confirmed with CTRL + SHIFT +ENTER

Kris
0

LVL 1

Author Comment

Nice:) thank you for explaining. I already looked for some explanation online, so added with your example now i think i understand it completely. It works like a charm.

Cheers,

Mark
0

## Featured Post

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.