Solved

Two Excel questions

Posted on 2001-09-10
6
155 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:EYoung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 12

Accepted Solution

by:
roverm earned 50 total points
ID: 6472349
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
 
LVL 12

Expert Comment

by:roverm
ID: 6472356
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
 
LVL 7

Author Comment

by:EYoung
ID: 6472467
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 7

Author Comment

by:EYoung
ID: 6472478
Do I need to specify the rows to sort or expand the Range option?  (Does Range("C1") mean sort all of column C?)
0
 
LVL 7

Author Comment

by:EYoung
ID: 6472554
Thanks.  It works just fine.
0
 
LVL 12

Expert Comment

by:roverm
ID: 6473267
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

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA filters 2 81
Getting warning: You are about to delete 1 row(s) 9 60
Access Object Property from VBA Module in Excel 2010 2 44
VB6 Enviroment 3 30
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question