excel - Trying to automate a process of sorting one column, all the way from "D:D" to "ZZ:ZZ" -

in short, have 5 hundred or columns, of sort alphabetically (with headers). although may have easier solution involving highlighting entire dataset , doing custom sort of sort.... i've taken stab @ writing script it. have far is:

sub columnsort()  dim rng range dim long  dim acell range    = 0  set rng = range("d:d")  while <= 5000  rng.offset(0, i).sort order1:=xlascending, header:=xlyes = + 1  end  wend  end sub 

you can see reasoning, line rng.offset(0, i).sort order1:xlascending, header: =xlyes giving me error. correct syntax use particular problem? there better way this?

thanks,

here's example of how sort columns 1-500 individually:

  dim col integer   dim r range   dim ws worksheet    set ws = activesheet    col = 1 500      set r = cells(1, col).entirecolumn      ws       .sort.sortfields.clear       .sort.sortfields.add key:=r, _           sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal       ws.sort         .setrange r         .apply       end     end    next col 

there lot of other settings can apply, depending on data , how want handle things (ie data have headers).

i can never remember them -- here record macro settings want , manually peel them out , add them code above.

for example, if wanted sort descending, have add order:=xldescending inner with clause.