Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to find last selected row in excel worksheet

Posted on 2005-02-25
5
Medium Priority
?
696 Views
Last Modified: 2008-01-09
Is there a way to figure out what is the last row in a selection on a worksheet?  Maybe someone can point me in the right direction.  

So if i select rows 1 to 13, in code i want to figure out that 13 is the last row in the selection.

Is there a way?

0
Comment
Question by:Lucas
5 Comments
 
LVL 17

Accepted Solution

by:
aflockhart earned 2000 total points
ID: 13404538
in VB code ?

selection.row     tells you the first row selected

selection.rows.count   tells you how many are selected



so


selection.row + selection.rows.count -1  

tells you the last row
0
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 13404557
Another option.

Dim R as String

r = range("A65535").end(xlup).row

Will return the last used row in Column A.

HTH
Cal
0
 
LVL 13

Author Comment

by:Lucas
ID: 13404723
Exactly what i was looking for THANKS A MILLION!
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 13404763
here some other macro's to find the last anything:

'Find the last used cell, before a blank in a Column:
Sub LastCellBeforeBlankInColumn()
   MsgBox Range("A1").End(xlDown).Address
End Sub

'Find the very last used cell in a Column:
Sub LastCellInColumn()
   MsgBox Range("A65536").End(xlUp).Address
End Sub

'Find the last cell, before a blank in a Row:
Sub LastCellBeforeBlankInRow()
    MsgBox Range("A1").End(xlToRight).Address
End Sub

'Find the very last used cell in a Row:
Sub LastCellInRow()
   MsgBox Range("IV1").End(xlToLeft).Address
End Sub

'Find the very last used cell on a Worksheet:
Sub Demo()
   MsgBox Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Address
End Sub

Sub NumberOfUsedRowsInA()
MsgBox RowsA = Cells(Rows.Count, 1).End(xlUp).Row
End Sub


or for the last row number in the selection

Sub LastRowInSelection()
For Each cel In Selection
    LastRow = cel.Row
Next cel
MsgBox LastRow
End Sub

regards,

Jeroen
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 13404882
keep in mind that the line: selection.rows.count shows only the number of rows which are selected. not the number of the last row which is selected

Jeroen
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
I came across an unsolved Outlook issue and here is my solution.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question