Prev: Restrict cells selected to specific range name
Next: visual c# excel 2007 pivot filter
From: Luc on 15 Dec 2009 13:42
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
From: Benito Merino on 15 Dec 2009 13:56
On 15 dic, 19:42, "Luc" <lf...(a)base.be> wrote:
> 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
From: Rick Rothstein on 15 Dec 2009 14:17
You can get the address with this one-liner....

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

--
Rick (MVP - Excel)


"Luc" <lferr(a)base.be> wrote in message news:OpE7IabfKHA.300(a)TK2MSFTNGP02.phx.gbl...
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
From: Luc on 15 Dec 2009 14:18
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" <benimerino(a)gmail.com> schreef in bericht
news:1839557b-aec8-4b5a-971e-365b25b50447(a)26g2000yqo.googlegroups.com...
On 15 dic, 19:42, "Luc" <lf...(a)base.be> wrote:
> 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

From: Rick Rothstein on 15 Dec 2009 14:27
And if you want to select that cell, just do this...

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


--
Rick (MVP - Excel)


"Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message news:uXwHQtbfKHA.1824(a)TK2MSFTNGP04.phx.gbl...
You can get the address with this one-liner....

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

--
Rick (MVP - Excel)


"Luc" <lferr(a)base.be> wrote in message news:OpE7IabfKHA.300(a)TK2MSFTNGP02.phx.gbl...
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
 |  Next  |  Last
Pages: 1 2
Prev: Restrict cells selected to specific range name
Next: visual c# excel 2007 pivot filter