Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 888
  • Last Modified:

last row number in range

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
   strLastCell = Cells(lastRow, LastCol).Address(False, False)

FindRangeRowColumn = strFirstCell + ":" + strLastCell

   lastRow = mRow
end function

Open in new window


thanks in advance
Cheers,

Mark
0
MarkVrenken
Asked:
MarkVrenken
  • 6
  • 3
  • 2
  • +1
2 Solutions
 
Elton PascuaCommented:
Not sure what your expected output is. What should it be based on the scenario below?

A27
0
 
MarkVrenkenAuthor Commented:
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
 
Shanan212Commented:
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
    MsgBox Cells(iRow, colTrap).Address
End Function

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
MarkVrenkenAuthor Commented:
Thanks Shanan, but i don't think this will work. it still will return 8 when there is something in D9
0
 
MarkVrenkenAuthor Commented:
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
 
Shanan212Commented:
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
 
MarkVrenkenAuthor Commented:
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
 
Shanan212Commented:
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
    MsgBox Cells(iRow, colTrap).Address
End Function

Open in new window

Temp.PNG
0
 
krishnakrkcCommented:
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
        x = .Address(external:=1)
        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()
    MsgBox FindRangeRowColumn(Range("A1:D9")).Address
End Sub

Open in new window


Kris
0
 
MarkVrenkenAuthor Commented:
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
 
krishnakrkcCommented:
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
 
MarkVrenkenAuthor Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now