VBA Sort Code - Changing Range

**ashleys.nl** · 10-14-2011, 12:40 PM

Hey Everyone,

I've been spending the better part of this day creating a Acronym search engine and "database" ( its an excel workbook). And I'm almost done with the help of everyone in the forum :D.

The last thing I want to do is once I have added a new acronym, by clicking the button and filling out the form. I want to add a piece of code that sorts the "Acronym Database" sheet, with regards to Column A.

I've been through the interet but can't find anything that does this with a changing range, since everytime I add a new acronym I add a new row.

Any ideas on how I could do this? I've attached my Excel Work Book for clarification.

Thanks

**MarvinP** · 10-14-2011, 01:10 PM

Hi ashleys.nl,

It sounds like you want a Dynamic Named Range to span all the rows of your data. Then use this range name in your sort formula.

http://www.ozgrid.com/Excel/DynamicRanges.htm
http://www.beyondtechnology.com/geeks007.shtml

**ashleys.nl** · 10-14-2011, 01:16 PM

Thanks Marvin,

But I can't really use add in's on our corperate computers. I'd like to have something that's coded into my VBA code.

Thanks for your input though

**MarvinP** · 10-14-2011, 01:22 PM

Hi,

Dynamic Named Ranges are not an Add-In to Excel. They live there but are just not used much. In VBA code you can find the last row that is used in a column with the following code:

LastRow = Cells(Rows.Count,"A").End(xlUp).Row

read http://www.tushar-mehta.com/excel/vb...and%20code.htm or
http://www.ozgrid.com/forum/showthre...t=68874&page=1

**TMShucks** · 10-14-2011, 01:23 PM

They're not references to Add-ins, they're explanations of how to define a Dynamic Named Range which caters for a variable number of rows.

Regards

**ashleys.nl** · 10-14-2011, 01:33 PM

Ahhh Okay,

I think I'm catching on,

I'm trying to implement this now into my code but Im getting the error :

"Compile Error : Method or data Member not Found"

Any thoughts?

Sub Sort()

Dim LastRow As Long

LastRow = Worksheets.Cells(Rows.Count, "A").End(xlUp).Row

Worksheets("Acronym Database").Sort.SortFields.Clear
Worksheets("Acronym Database").Sort.SortFields.Add Key:=Range( _
        "A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With Worksheets("Acronym Database").Sort
        .SetRange Range("A1:LastRow")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub