Find and select LastRow Minus 1

[Carlie]

I am trying to wirte a macro to find and select a range based on the
first and last row (minus one row) of data on a spreadheet

Does anyone know how to incorporate myLastRow with minus 1 row?

Thanks in advance.


Range("A4").Select
myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns,
xlPrevious).Column
myLastCell = Cells(myLastRow, myLastColumn).Address

 

[Toppers]

Like?

myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row-1

 

[Mark Lincoln]

If you want myLastCell to refer to the previous row, just do this:

myLastCell = Cells(myLastRow - 1, myLastColumn).Address

or you can make myLastRow equal to the row you need (if you need it for
nothing else) by:

myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row - 1

 

[Ronald Dodge]

One thing you can do is use the End function of the Range Object, provided
you use that worksheet more or less specifically for that list. Examples:

What is the very last row used within column A?

MyLastRow = Range("A65536").End(xlUp).Row

What is the very last column used within row 4?

MyLastColumn = Range("IV4").End(xlLeft).Column

Note: In the first example, it's the same as if the code started with Cell
A65536, then pressed Ctrl and the Up Arrow simultaniously. At this point,
if you still need to know the previous row, you can use the following:

MyLastRow = Range("A65536").End(xlUp).Row-1

Also, the Column property is numeric, not a letter, so you would use the
Cells Object rather than the Range Object like:

Cells(MyLastRow,MyLastColumn)

I would also be careful about using these without prequalifying them with a
worksheet object as that can lead into some unwanted issues.

 

[Carlie]

Thanks guys. I went for the:

myLastCell = Cells(myLastRow - 1, myLastColumn).Address

and works fine