Find Last Used Row in a Range

[John Vieren]

Hi,

Been working this for a while.
Can find VBA for entire column but not a range.
Came up with the code below, after looking at excel formulas.
Seems to work except if all cells in my range are empty.
Any thoughts on how to fix this or alternate code?
Thanks For your help, JV

Please Login or Register  to view this content.

[thomglea]

Please Login or Register  to view this content.

That will find the last cell that has something in it. From there you can pull

Please Login or Register  to view this content.

[wk9128]

Pls try this code

Please Login or Register  to view this content.

[John Vieren]

Hi Thomglea,

Need to read up on range find as never used it before.
What:="*" means search for anything?
How does fndrng.row find the last used row?
Tested the code, works but not if range is blank.

Thanks for your help, John

[thomglea]

"*" is a wildcard. Means it will hit on anything. Only thing to think about there is if the cell has a formula, but the formula evaluates to a blank, it will return that cell. Easy to fix by adding LookIn:=XlValues
fndrng.row does not find the last row, the .Find function finds the cell in the last row. fndrng.row is a method that returns the row number.

[John Vieren]

Thank you.

I saw this approach on entire column, but could not figure out how to apply to a range.
I tested the code below, but seems that VBA does not accept this.

Please Login or Register  to view this content.

[John Vieren]

I see, interesting.
Thank you for explaining.

[bebo021999]

Another way:
The range could be column or range

Option Explicit 
Sub findLastRow() 
Dim i&, rng As Range 
Set rng = Range("F6:Z100") 
For i = rng.Cells.Count To 1 Step -1 
    If rng.Cells(i) <> "" Then 
        MsgBox rng.Cells(i).Row 
        'MsgBox rng.Cells(i).address ' return cell address 
        Exit Sub 
    End If 
Next 
MsgBox "The range is empty!" 
End Sub  

[jindon]

Use of formula

Please Login or Register  to view this content.

[John Vieren]

Thank you bebe021999 and Jindon for your soutions.

Your solutions are beyond my VBA understanding, sorry but much appreciated.

I refined my original code and is now doing what I was looking for.

Thank you all for your help.

Please Login or Register  to view this content.

[John Vieren]

Hi Jindon,

If i understand this correctly, this seems to be an excel formula translated to VBA code.
I considered this idea, but my VBA skills are to insuficient.
Thank you for taking the time to post this.
Kindest regards john

[jindon]

You can check.

Enter any cell other than F6:F16

=MAX(MAX(IF(F6:F16<>"",ROW(F6:F16))),6)

[John Vieren]

Hi Jindon,

OK undrstood.
I was using this excel formula =MAX(IF(ISBLANK(F6:F25),0,ROW(F6:F25)))
Found it to complex to translateto VBA code.
That now seems doable with your code example.
Regards john

[John Vieren]

Hi bebo021999,

I almost understand this code, but still a bit foggy.
Basically you start in the last row/column of the range and loop backwards from the total number of cells by -1.
If the cell i not blank you provide the row number.
I dont see where it gets the maximum row number, can you explain please?
Also is this part code or text .....'MsgBox rng.Cells(i).address ' return cell address?

Thanks for your help, John

[daboho]

Modify your code

Please Login or Register  to view this content.

[wk9128]

Hi John Vieren
POST # 3 code has been updated, please test it


If you finally get a solution please mark your thread as SOLVED:
- Click Thread Tools above your first post,you will see the word PREFIX on the upper left, press the button to select [SOLVED] select "Mark your thread as Solved".
- you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help
was given. By doing so you can add to the reputation(s) of those who helped.

[John Vieren]

Hi wk9128,

Updated code is working fine, thank you.
Was not aware of the "add to reputation" and have done so for all who assisted me, thank you.
The thread has previously been marked as solved.
Kindest regards John

[wk9128]

@John Vieren You're Welcome. Glad to help . Thank You for the feedback and rep.

Please Login or Register  to view this content.

[John Vieren]

wk9128,

I see, looping backards from bottom right hand corner of column F and first cell that does not have a blank is the last row.
I did not expect such a massive response to my posting and learned quite alot of VBA as a result.
Am very grateful and thank you all, JV

[bebo021999]

Hi bebo021999,
Basically you start in the last row/column of the range and loop backwards from the total number of cells by -1.
If the cell i not blank you provide the row number.
I dont see where it gets the maximum row number, can you explain please?
Also is this part code or text .....'MsgBox rng.Cells(i).address ' return cell address?

The array rng (F6:Z100) contains hundreds of cells, with each cell represented as rng.cells(i), where i counts from 1 to the last cell.

Looping from the last cell backward to 1, when encountering the first non-empty cell, display a MsgBox, then stop the program.

Please Login or Register  to view this content.

If the loop completes without encountering an empty cell, then the final message will be displayed:

Please Login or Register  to view this content.

Hope it is clear enough for you

[John Vieren]

Very clear, thank you.