Find last cell after paste

[mob....@gmail.com]

If I copy and paste a block of rows from a text file onto an Excel sheet (starting at cell A1) how can I determine the row number that contains the last row of text?

For example, if I post 120 sentence rows onto a sheet, the last row number will be 120.

Thanks!

[mob....@gmail.com]

On Saturday, November 5, 2022 at 11:06:37 PM UTC-7, I wrote:
>
> For example, if I post 120 sentence rows onto a sheet, the last row number will be 120.
>

In other words, I basically need to calculate the number of rows that get pasted onto an Excel sheet. I hope that makes better sense.

[Auric__]

Assuming you're actually using Application.Paste, immediately after the paste
operation, check Selection.Address, which will give the address in the form
$A$1:$A:$2. You can then retrieve just the bottom row using InStrRev:

ActiveSheet.Paste
M = Selection.Address
Result = Mid(M, InStrRev(M, "$") + 1)

Result should now contain just the row number of the bottom row.

Alternately, if you just want the bottom row of the entire spreadsheet, use
Cells.SpecialCells(xlCellTypeLastCell).Row.


If you're pasting in the data using any other manner, well, it depends on how
you're doing it.

--
Conversation is not only the vehicle of thought,
it is a tremendous and efficient instrument in thinking.