Find last row in a dynamic range

[Vincent88]

Hi Guys,
I am using this code to find the last row in a dynamic range lastRow = Range("A3").End(xlDown).Row but it's really not what I prefer If my current dynamic range is for example from A3 to A14 to AL rows. If I enter something in let say in cell A17, the last row will becom A17.
How to rewrite the code to really reflect the true last row of the current dynamic range ?

 

[alansidman]

lastRow= Range("A" & rows.count).end(xlup).Row

 

[johnnyL]

Why would you check for a last row, add an additional row further down, then complain that the first result is wrong?

 

[johnnyL]

I guess you could put the code in a change event macro if you want the lastRow to be updated 'Live' so to speak.

 

[DanteAmor]

but it's really not what I prefer If my current dynamic range is for example from A3 to A14 to AL rows. If I enter something in let say in cell A17, the last row will becom A17.

This line will give you the last row with data from column A.
lr = Range("A" & Rows.Count).End(xlUp).Row

But if you have data in A17 and D24, the last actual row with data then is 24.
If you don't know which column is the last row with data, then I show you 2 options:

  lr = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
  lr = ActiveSheet.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row

 

[Vincent88]

This line will give you the last row with data from column A.
lr = Range("A" & Rows.Count).End(xlUp).Row

But if you have data in A17 and D24, the last actual row with data then is 24.
If you don't know which column is the last row with data, then I show you 2 options:

  lr = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
  lr = ActiveSheet.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row

Hi DanteAmor, are your codes possible to specify the count is from column A1 or A3 ?

 

[johnnyL]

@Vincent88 See if the following formulas help you determine what you are looking for:

       LastRowInSheet = Sheets("Sheet1").Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row                      ' Returns a Row Number
       LastRowInRange = Sheets("Sheet1").Range("A1:D14").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row            ' Returns a Row Number
'
    LastColumnInSheet = Split(Cells(1, (Sheets("Sheet1").Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column)).Address, "$")(1)   ' Returns a Column Letter
    LastColumnInRange = Split(Cells(1, (Sheets("Sheet1").Range("A:E").Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column)).Address, "$")(1)  ' Returns a Column Letter
        LastCellInRow = Split(Cells(1, (Sheets("Sheet1").Rows(13).Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column)).Address, "$")(1)  ' Returns a Column Letter

 

[DanteAmor]

Hi DanteAmor, are your codes possible to specify the count is from column A1 or A3 ?

I do not understand what you mean.
Can you explain with an image what is the end goal?

 

[Vincent88]

Why would you check for a last row, add an additional row further down, then complain that the first result is wrong?

Hi JohnnyL,
I did not say that the code is wrong. I use it in other workbooks quite frequent. The reason I ask for alternative source is that I may input something as note in column A below the range, the current lastrow code affects my other vba functions.

I do not understand what you mean.
Can you explain with an image what is the end goal?

Hi DanteAmor,
JohnnyL answered my quest already. Thank you. Will try the code.

@Vincent88 See if the following formulas help you determine what you are looking for:

       LastRowInSheet = Sheets("Sheet1").Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row                      ' Returns a Row Number
       LastRowInRange = Sheets("Sheet1").Range("A1:D14").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row            ' Returns a Row Number
'
    LastColumnInSheet = Split(Cells(1, (Sheets("Sheet1").Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column)).Address, "$")(1)   ' Returns a Column Letter
    LastColumnInRange = Split(Cells(1, (Sheets("Sheet1").Range("A:E").Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column)).Address, "$")(1)  ' Returns a Column Letter
        LastCellInRow = Split(Cells(1, (Sheets("Sheet1").Rows(13).Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column)).Address, "$")(1)  ' Returns a Column Letter

Will try to see how they work. Thanks

 

[EXCEL MAX]

Hello Vincent 88,
if you want to find last row in some range use find method but,
specify the range for search and in the find function as second argument set the last row of that range.

Sub FindRealLastRow()

    Dim vR As Long
    
    vR = [A3:A20].Find("*", [A20], , , , xlPrevious).Row
    
End Sub