Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Cut and move columns, need help with set statement (Excel xp)

    I'm working on a macro to sort columns C to AR based on what is in row 2 and 3. I have that part done. The columns get sorted correctly by the region (row 2) and then store number that is in row 3. The problem is that some of the stores have a zero value in row 7. If there is a zero value i need to move it to the end of the "region" section. There are 4 regions. Region 2 starts in column M, Region 3 in column S and region 4 in column AG. Region 1 starts in column C.

    So for instance, G7 has a zero in it and i need to cut the G column and insert it in front of the M column.

    I've attached a sample. Thank you for the help.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cut and move columns, need help with set statement (Excel xp)

    I would do it as 2 routines:
    The first checks the value in row 7. If it is "0" then set the range as the column, and check the value in row 2. Go down to the right until you change region (it might be better to separate the "row" into 2 rows a "region" and a "subregion", since that is an easier check than checking the first part of the cell.

    Once it finds the "change" it moves the column. Then it goes back to where it was and continues the checking and moving.

    After this "routine" to move the columns is done, run your routine to sort the columns.

    One comment, I don't know how many rows you have in your "full data set", but as it stands, I think the data "screams" to be transposed. This would allow data filtering and put more on a sheet. Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cut and move columns, need help with set statement (Excel xp)

    Try something like the following. This macro uses the cells is C1:Ar1 to temporarily store an additional value to sort on. If there is any chance that these cells will ever be used for anything else, you will need to modify the macro to use other cells.

    <pre>Public Sub SortCols()
    Dim oCell As Range
    For Each oCell In ActiveSheet.Range("C7:AR7")
    If oCell.Value = 0 Then
    oCell.Offset(-6, 0).Value = 1
    Else
    oCell.Offset(-6, 0).Value = 0
    End If
    Next oCell
    ActiveSheet.Range("C1:AR8").Sort Key1:=ActiveSheet.Range("C2"), Order1:=xlAscending, _
    Key2:=ActiveSheet.Range("C1"), Order2:=xlAscending, _
    Key3:=ActiveSheet.Range("C3"), Order3:=xlAscending, _
    Orientation:=xlSortRows
    ActiveSheet.Range("C1:AR1").Clear
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •