List sorting and macros



Hey guys n gals,

Happy New Year!

I am creating a macro to alphabetize a list of names that will slowly
increase in length over time.

I know where the first cell in the list is, but I do not know where the
end is.

I use a named range to print my report references.

Ocaissonally I add a name and want to use this macro to sort the list.

My problem is that I always will want to select all the non-empty cells
within that named range.

I know there is a string which takes me to the last filled cell in a
column, but I cannot remember it.

So, I want my script to select the entire filled data set within named

This is my current recording, which selects directly, without the named
range (which always starts at C18. So the script I need could ignore the
named range, and simply step down to the last filled cell.

My desire is to highlight that list, before the A to Z sort. The list
is always alpha and zero numerics.

Unless there is some shorter script for selecting and sorting a single
column list which also includes header fields, etc. This is why I
thought that a named range would work.

But I also have blank cell in that range which makes for blank lines in
the report so that they can be hand filled (it is a daily test data log)

Sub AlphaTize()
' AlphaTize Macro
' Alphabetize the long name listing
Key:=Range("C80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
With ActiveWorkbook.Worksheets("Info_Page").Sort
.SetRange Range("C19:C80")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With
End Sub




hi ,

Dim LastRow As Long

With Sheets("Info_Page")
LastRow = .Cells(.Rows.Count, 3).End(xlUp).Row
..Range(.Cells(18, 3), .Cells(LastRow, 3)).Select
End With


Le 2013-01-05 12:03, CellShocked a écrit :


Thank you.

You're a saint.

I knew it was simple, but so is my expertise here.
I use them, I do not get paid to sit all day making them. (though I
should have remained a database developer, considering what my career
choice has dealt me over the years)




So, I see how this selects the filed cells in a column. I could not
correctly integrate it into my macro though.

Is there also a short A to Z sort VB function that would kill all that
recorded macro stuff, if it isn't needed?

My recording references the range shown. I do not know how to
integrate your "with" into it.

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

Similar Threads

Applying Variables to SORT 4
Problem with Worksheet Activate and Sorting 7
Undo Macro Action 3
Sorting Question 5
Clear Check Box 2
Sort Macro Compatibility 3
VBA 2 Codes 2
writing a sort macro 2