How do i select the coordinates of the first column & last row in a pasted range

L

Luc

When i have a range pasted to a worksheet, how do i retrieve the coordinates of the last row, first column of this range (the range contains empty cells).

Example of a range : B3:F32 => the result shuld be B32

The range is not fixed, it could contain 1 row to ..... rows
PS : The range is pasted and selected


Thanx for your help.

Luc
 
B

Benito Merino

When i have a range pasted to a worksheet, how do i retrieve the coordinates of the last row, first column of this range (the range contains empty cells).

Example of a range : B3:F32  => the result shuld be B32

The range is not fixed, it could contain 1 row to ..... rows
PS : The range is pasted and selected

Thanx for your help.

Luc

Hello.

One way is:

Sub lastrowfirstcolumn()
With Selection
first_column = .Column
last_row = .Row + .Rows.Count - 1
coord = Cells(last_row, first_column).Address
End With
End Sub

Regards,

Benito
Barcelona
 
R

Rick Rothstein

You can get the address with this one-liner....

LastRowFirstColAddress = Selection(1)(Selection.Rows.Count).Address(0, 0)

--
Rick (MVP - Excel)


When i have a range pasted to a worksheet, how do i retrieve the coordinates of the last row, first column of this range (the range contains empty cells).

Example of a range : B3:F32 => the result shuld be B32

The range is not fixed, it could contain 1 row to ..... rows
PS : The range is pasted and selected


Thanx for your help.

Luc
 
L

Luc

Thanx Benito,

What code should i use to move to this last cell + 1 row lower ?
And make this the active cell.

In my Example that would be B32 + 1 row => B33






"Benito Merino" <[email protected]> schreef in bericht
When i have a range pasted to a worksheet, how do i retrieve the
coordinates of the last row, first column of this range (the range
contains empty cells).

Example of a range : B3:F32 => the result shuld be B32

The range is not fixed, it could contain 1 row to ..... rows
PS : The range is pasted and selected

Thanx for your help.

Luc

Hello.

One way is:

Sub lastrowfirstcolumn()
With Selection
first_column = .Column
last_row = .Row + .Rows.Count - 1
coord = Cells(last_row, first_column).Address
End With
End Sub

Regards,

Benito
Barcelona
 
R

Rick Rothstein

And if you want to select that cell, just do this...

Selection(1)(Selection.Rows.Count).Select


--
Rick (MVP - Excel)


You can get the address with this one-liner....

LastRowFirstColAddress = Selection(1)(Selection.Rows.Count).Address(0, 0)

--
Rick (MVP - Excel)


When i have a range pasted to a worksheet, how do i retrieve the coordinates of the last row, first column of this range (the range contains empty cells).

Example of a range : B3:F32 => the result shuld be B32

The range is not fixed, it could contain 1 row to ..... rows
PS : The range is pasted and selected


Thanx for your help.

Luc
 
L

Luc

Perfect , thanxxxxs for all the help.
If i hadn't you guys.......


Luc
"Rick Rothstein" <[email protected]> schreef in bericht And if you want to select that cell, just do this...

Selection(1)(Selection.Rows.Count).Select


--
Rick (MVP - Excel)


You can get the address with this one-liner....

LastRowFirstColAddress = Selection(1)(Selection.Rows.Count).Address(0, 0)

--
Rick (MVP - Excel)


When i have a range pasted to a worksheet, how do i retrieve the coordinates of the last row, first column of this range (the range contains empty cells).

Example of a range : B3:F32 => the result shuld be B32

The range is not fixed, it could contain 1 row to ..... rows
PS : The range is pasted and selected


Thanx for your help.

Luc
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top