VBA for Last Row

[ionelz]

Hi,

Found this on Internet for last row

XRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Looks like it count OK the last row if there is data in Coulmn A, which is not the case all the time

How can this be changed if I know that all the time in row there is data in Column 80
or better
How can this be changed to look for last row with data in range A7:EZ5000

Thanks

[TMS]

Try:

Please Login or Register  to view this content.

[protonLeah]

"...data in Column 80 or better..."

Not knowing the column, maybe:

Please Login or Register  to view this content.

[TMS]

Bear in mind that UsedRange can be unreliable and can return a higher last row number than actual data rows.

[Rick Rothstein]

If you don't know which column will have the last value...

For last value
---------------
LastUsedRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
LastUsedCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column


For last value or formula
------------------------------
LastUsedRow = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row
LastUsedCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column


A note about those headings... the ones labeled "For last value" means it will return the last used row and column for a displayed value (whether a constant or one from a formula) even if there are formulas after those cells displaying the empty string (""); whereas those labeled "For last value or formula" returns the last cell with either a value or a formula even if that formula is displaying the empty string ("").

[jolivanes]

Take your pick

Please Login or Register  to view this content.

Next time I'll use the F5 key Rick to see that you just previously posted!!!!!!!!

[ionelz]

Could you please help on this :
This one work ! XRow = Sheet1.Cells(Rows.Count, 80).End(xlUp).Offset(1, 0).Row
How can I replace 80 with value from Cell A1 ?

[protonLeah]

Please Login or Register  to view this content.

[TMS]

You're welcome. Thanks for the rep.