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
range.

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
'
Range("C18:C80").Select
Range("C80").Activate
ActiveWorkbook.Worksheets("Info_Page").Sort.SortFi elds.Clear
ActiveWorkbook.Worksheets("Info_Page").Sort.SortFi elds.Add
Key:=Range("C80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Info_Page").Sort
.SetRange Range("C19:C80")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

List sorting and macros
 

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

isabelle

Le 2013-01-05 12:03, CellShocked a écrit :
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
range.

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
'
Range("C18:C80").Select
Range("C80").Activate
ActiveWorkbook.Worksheets("Info_Page").Sort.SortFi elds.Clear
ActiveWorkbook.Worksheets("Info_Page").Sort.SortFi elds.Add
Key:=Range("C80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Info_Page").Sort
.SetRange Range("C19:C80")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

List sorting and macros
 

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)

On Sat, 05 Jan 2013 12:38:47 -0500, isabelle wrote:

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

isabelle

Le 2013-01-05 12:03, CellShocked a écrit :
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
range.

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
'
Range("C18:C80").Select
Range("C80").Activate
ActiveWorkbook.Worksheets("Info_Page").Sort.SortFi elds.Clear
ActiveWorkbook.Worksheets("Info_Page").Sort.SortFi elds.Add
Key:=Range("C80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Info_Page").Sort
.SetRange Range("C19:C80")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

List sorting and macros
 

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.




On Sat, 05 Jan 2013 09:47:31 -0800, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet. org wrote:


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)

On Sat, 05 Jan 2013 12:38:47 -0500, isabelle wrote:

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

isabelle

Le 2013-01-05 12:03, CellShocked a écrit :
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
range.

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
'
Range("C18:C80").Select
Range("C80").Activate
ActiveWorkbook.Worksheets("Info_Page").Sort.SortFi elds.Clear
ActiveWorkbook.Worksheets("Info_Page").Sort.SortFi elds.Add
Key:=Range("C80") _
, SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Info_Page").Sort
.SetRange Range("C19:C80")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub