Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / October 2007

Tip: Looking for answers? Try searching our database.

Matcro to order columns

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ric0999 - 10 Oct 2007 13:48 GMT
Please, i need help with a macro to order 2 columns like that:

Column A       Column B
     A                   A
     B                   B
     C                   D
     E                   E
     G                   F

After the macro runs, the columns will be in the follow order:

Column A       Column B
     A               A
     B               B
     C
                      D
     E               E
                      F
     G

Tks for this help.
Dave Peterson - 10 Oct 2007 14:07 GMT
Option Explicit
Sub testme()

   Application.ScreenUpdating = False
     
   Dim wks As Worksheet
   Dim ColA As Range
   Dim ColB As Range
   Dim iRow As Long
   Dim myCols As Long
   
   Set wks = Worksheets("sheet1")
   wks.DisplayPageBreaks = False
   With wks
       'row 1 has headers!
       Set ColA = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
       Set ColB = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
               
       With ColA
           .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
       End With
     
       'change the mycols to the number of columns that
       'are associated with column B
       
       myCols = 1 ' columns B only
       With ColB.Resize(, myCols)
           .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
       End With
       
       iRow = 2
       Do
           If Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 0 Then
               Exit Do
           End If
           
           If .Cells(iRow, "A").Value = .Cells(iRow, "B").Value _
            Or Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 1 Then
               'do nothing
           Else
               If .Cells(iRow, "A").Value > .Cells(iRow, "B").Value Then
                   .Cells(iRow, "A").Insert shift:=xlDown
               Else
                   .Cells(iRow, "B").Resize(1, myCols).Insert shift:=xlDown
               End If
           End If
           iRow = iRow + 1
       Loop
   End With
   
   Application.ScreenUpdating = True
   
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

========
Remember:  Both these techniques are useful if there are no duplicates in each
of the columns.

> Please, i need help with a macro to order 2 columns like that:
>
[quoted text clipped - 17 lines]
>
> Tks for this help.

Signature

Dave Peterson

Ric0999 - 10 Oct 2007 15:59 GMT
Tks Dave.

I will study the code and adapt to my case.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.