Two Excel questions

Basics:  VB6 sp5, Windows/NT, Excel 2000

In my VB application I create and populate an Excel worksheet successfully.  Now I want to do two additional things to the spreadsheet as follows:

1.  How do I sort the spreadsheet based on two columns?  Column A should be ascending and Column B descending.  However, the sort should start with row 3 as rows 1 and 2 are column headers and thus should not be sorted.

2.  How do I right justify a certain cell in the spreadsheet?

Thanks for the help.
LVL 7
EYoungAsked:
Who is Participating?
 
rovermConnect With a Mentor Commented:
This is the code for sorting:

Sub Sortit()
Dim oSheet As Worksheet
Dim oCurSheet As Worksheet

    'first move first 2 rows to temporary sheet:
    Set oCurSheet = ActiveSheet
    Rows("1:2").Copy
    Set oSheet = ActiveWorkbook.Sheets.Add
    oSheet.Activate
    Rows("1:2").Select
    ActiveSheet.Paste
   
    'then delete the first rows
    oCurSheet.Activate
    Rows("1:2").Delete Shift:=xlUp
   
    'sort it
    Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
        , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
       
    'get the first rows back
    oSheet.Activate
    Rows("1:2").Copy
    oCurSheet.Activate
    Rows("1:1").Insert Shift:=xlDown
   
    'now delete temp sheet
    Application.DisplayAlerts = False
    oSheet.Delete
    Application.DisplayAlerts = True
   
    Application.CutCopyMode = False
   
    Set oSheet = Nothing
    Set oCurSheet = Nothing
   
End Sub

And this for the alignment of a cell:

cells(1,1).HorizontalAlignment = xlRight

D'Mzzl!
RoverM
0
 
rovermCommented:
Of course, above code was written in VBA (Excel) just alter it to run from VB or place the code in the Excel workbook and run it as an macro (Application.Run "Sortit").

D'Mzzl!
RoverM
0
 
EYoungAuthor Commented:
roverm,

I tried to sort the spreadsheet using the following code but it does not appear to be working.  Do I need all of the parameters, i.e. Header, OrderCustom, Orientation, etc.?  (For testing purposes I am including the first two rows in the sort just to see how it works.)

    W.Cells.Sort Key1:=Range("C1"), Order1:=xlAscending, Key2:=Range("E1") _
       , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
       False, Orientation:=xlTopToBottom


Thanks
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
EYoungAuthor Commented:
Do I need to specify the rows to sort or expand the Range option?  (Does Range("C1") mean sort all of column C?)
0
 
EYoungAuthor Commented:
Thanks.  It works just fine.
0
 
rovermCommented:
Your first comment:
No, you don't need them all, every prop got it's own default. Like Orientation = xlTopBottom.
Just try to leave one out and see ;-)

Second comment:
Yes, Range("C1") means ON what column to sort. To select a range:
Columns("A1:E1").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Last comment:
Thanks for the points !

Glad I could help.

D'Mzzl!
RoverM
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.