sort specified column on excel

guys,
 how to sort a specified column in excel?

thanks
love
lovemetoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
HemanthaKumarConnect With a Mentor Commented:
Try this

Set Sheet = Excel.Application.ActiveWorkbook.Sheets.Application.Worksheets("Vamshee")
Call Sheet.Activate
Set rSortKey = ActiveSheet.Range("E1")       ' Sort Column 1st value
Set rSort = ActiveSheet.Range("E:E")           'Sort based on this column
rSort.Select
Set aRange = ActiveSheet.UsedRange
Call aRange.Sort(rSortKey, xlAscending)

~Hemanth
0
 
Sjef BosmanGroupware ConsultantCommented:
From Notes? Are you in the right forum? If you are, then we need a lot more info...
0
 
lovemetoAuthor Commented:
sorry if my question is not that clear.
here is my situation

i have a lotus script that generate report to excel using db.search, because i am using db.search the data is not sorted so i need  to sort this using code that sort a specified column in excel.

thanks
love
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Sjef BosmanGroupware ConsultantCommented:
Ah, I understand. Two approaches:
a. use a view that's already sorted the way you want; by the way, db.search is VERY inefficient...
b. do it the way you described; I'd have to look up how to write the correct VB-script, but the easiest way for you is to record a macro, click in the spreadsheet how you want it sorted, then stop recording and open the macro-editor (Alt-F11); you then see the code you have to simulate from LotusScript
0
 
lovemetoAuthor Commented:
but how i cant get your instruction,

thank you
0
 
lovemetoAuthor Commented:
a:using a view is not applicable on my application, so i need a dbsearch,
b: but how ? i cant get your instruction. im not good in excel macro

thanks


0
 
Sjef BosmanGroupware ConsultantCommented:
You don't need to be good at VBA, the macro buttons will do it for you. I assume you have a COM-object in LotusScript? Then you can adapt the code generated in Excel to your needs.

- So generate the (non-sorted) spreadsheet
- Open it in Excel
- Click on Extra/Macro's/Record new macro (maybe it's not called Extra in your version)
- select the whole spreadsheet
- click on Data/Sort and set it up to sort the way you want
- when sorting is finished, click on the macro stop recording button
- then type Alt-F11
0
 
lovemetoAuthor Commented:
soryy but i can get you instruction i cant see any changes on the properties of spreadsheet,

here is one thing i already have code using other code thant can fomat cell and row the only thing that i dont have is the code that sort a column.

here is my sample code

                       .application.activesheet.PageSetup.PrintTitleRows = "$1:$4"
            .application.activesheet.PageSetup.PrintTitleColumns =""
            .application.activesheet.PageSetup.LeftHeader = ""
            .application.activesheet.PageSetup.CenterHeader = ""            .application.activesheet.PageSetup.RightHeader = ""
            .application.activesheet.PageSetup.LeftFooter = ""
            .application.activesheet.PageSetup.CenterFooter = "Page &P"
            .application.activesheet.PageSetup.RightFooter = "&D"
            .application.activesheet.Pagesetup.Orientation = 2
            .application.activesheet.Pagesetup.PaperSize = 5


example 2


                                 Selection.HorizontalAlignment = xlLeft
            .Selection.VerticalAlignment = xlTop
            .Selection.WrapText = True
            .Selection.Orientation = 0
            .Selection.ShrinkToFit = False
            .Selection.MergeCells = False
            .Selection.Borders.LineStyle = 1
            .Selection.Borders.Weight = 2
            .statusbar="Completed ..."

but no code for column formating,


thank you


0
 
lovemetoAuthor Commented:
sorry but i cant get your instruction i cant see any changes on the properties of spreadsheet
0
 
lovemetoAuthor Commented:
can u please give me the code i need this one very badly, thanks a lot!
0
 
qwaleteeCommented:
Hi lovemeto,

You are wrong.  A view IS appropriate to your situation.  Whatever your DBSearch formula is can be the SELECT formula for a view.

And what Sjef is talking about is as follows:

    - open the unsorted spreadhseet using teh regular Excel UI
    - tools->macro->record
    - select the columns/rows to be sorted
    - data -> sort
    - sort it
    - stop recording
    - tools -> macros
    - edit your newly-recorded macro

The statements you see are the exact statement you will use in LotusScript EXCEPT:
1) remove all the "paramater name :=" stuff, just leave the parts between the := and the , (and the , itself)
2) if there are any constants used as parameters, you will haveto find their value.  just create a new sub in the macro editor, and use msgbox PARAMETER_CONSTANT_NAME and run that sub.  It will display the value of that constant

Now, you can bring the same code over to LotusScript.
   

Cheers!
0
 
Sjef BosmanGroupware ConsultantCommented:
Hey, I don't have the code. And I'm not going to create it either. I know how to get there, but you don't seem to understand what I'm writing. Are you familiar with Excel basics, or not? I don't mean to be insulting, I like to help, but I don't like you trying to use my brains without trying your own brains first.
0
 
lovemetoAuthor Commented:
hermanth your code is working, but i want to specified where row it will going to start becuase  when i try your code, the title goes down. thanks .
0
 
qwaleteeCommented:
Disservice.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.