Need to find the bottom of a list in an Excel column 1

[JESTAR]

I need to find the bottom cell of list that is always getting longer, through VBA.

A macro is first run to sort the data alphabetically.

e.g.
A
NAME
Bob
Frank
John
Mike

...I need to get "Mike".

BtnName_Click()
Dim Row as Integer
Dim Names as Object
Set Names = Sheets("Sheet1&quot.Cells

While Not IsEmpty (Names(Row, 1))


.... something something.

 

[JESTAR]

Oops, wait, I didn't post all my messed up code...

 

[JESTAR]

Okay.... this crashed Excel, probably looping all the way through the worksheet...

While Not IsEmpty(Names(Row, 1))
Row = Row + 1
While IsEmpty
LblLastName.Caption = (Row = Row - 1)
Wend
Wend

My logic there was to read down column A until an empty cell was found, move back up 1 cell (which would be the last name) and stick that in a label caption. Excel crashed.

 

[DaleWatson123321]

JESTAR,

Here's one option...

Sub SelectLastName()
Application.Goto Reference:="topcell"
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub

Where "topcell" is the top cell of your range - the one containing the label "Name".

Hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

JESTAR,

Here's another option that can sometimes be useful. It goes "from the bottom up".

This will work for ANY column - to which you assign the range name "topcell".

Sub SelectLastName()
Application.Goto Reference:="topcell"
LastCell = Cells(65536, ActiveCell.Column).Address
Range(LastCell).End(xlUp).Select
End Sub

Hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JESTAR]

Thanks for your help Dale. It's giving me a "Reference not valid" on Application.Goto Reference:="A1"..... or the same error on Range(ActiveCell, ActiveCell.End(xlDown)).Select. It says the values for ActiveCell.End is the last filled cell in the column though, which is good.

 

[DaleWatson123321]

JESTAR,

I had referenced a cell named "topcell" in my example.

I have been a longtime proponent of the use of assigning "range names". However, if you want to reference a particular cell by using the cell coordinate, then use the following.

Sub SelectLastName()
Range("A1&quot.Select
LastCell = Cells(65536, ActiveCell.Column).Address
Range(LastCell).End(xlUp).Select
End Sub

or...

Sub SelectLastName()
Range("A1&quot.Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub

The reasoning behind the use of range names is... It most often means you won't have to modify your code each and every time to make modifications to an "evolving" model.

Interntally, Excel maintains a link between the range names and the cells to which they're assigned. Therefore, one can freely insert/delete rows/columns or move data from place to place. Naturally, however, if one were to delete an ENTIRE range where a range name is assigned, the range name would be deleted.

Method of Assigning a Range Name:
a) Highlight the cell or range-of-cells
b) Hold down <Control> and hit <F3>
c) Type the name
d) Hit <Enter>

Hope this helps. Please advise as to whether you get it to work.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JESTAR]

Thanks again, those ranges will help in another project

Sub SelectLastName()
Dim LastCell as String
Range(&quot;A1&quot.Select
LastCell = Cells(65536, ActiveCell.Column).Address
Range(LastCell).End(xlUp).Select
End Sub

Private Sub BtnName_Click()
SortByNames
LblLastName.Caption = LastName
End Sub

--------

Last name: $A$65536

It's referencing the entire column, or something, but thanks for the help, bedtime, have a star.

 

[DaleWatson123321]

JESTAR,

Thanks for the &quot;STAR&quot;.



Sub SelectLastName()
Range(&quot;A1&quot.Select
LastCell = Cells(65536, ActiveCell.Column).Address
Range(LastCell).End(xlUp).Select
LastName = ActiveCell.Value
MsgBox LastName
End Sub

The routine I gave you only &quot;selected&quot; the cell in which the Last Name would be - &quot;Mike&quot; in your example. The variable &quot;LastCell&quot; is only the point (A65535) from which the routine goes &quot;.End(xlUp)&quot; to Select the last cell in Column A that contains data.

I wasn't sure what you wanted to do at that point, so I ended the example at that point.

This latest modification will give you the name &quot;Mike&quot; in a Message Box - if &quot;Mike&quot; is the last name in column A.

I hope this works for your. Please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JESTAR]

Nice job, it worked great. Sorry for the unclearness, it's late, and watching the Anna Nicole Smith show has killed some brain cells.

Cheers fella.