+ Reply to Thread
Results 1 to 3 of 3

[SOLVED] need help righting script to sort rows alphabetically then seperate them to

  1. #1
    Kamachi923@gmail.com
    Guest

    [SOLVED] need help righting script to sort rows alphabetically then seperate them to

    Hello, can anybody please help me I am new to script righting and was
    trying to figure out how to sort my table.

    I have a table that is at least 800 rows and about 5 columns wide.
    short example of a row would be

    ROW column B column C column D column E
    1 j1-2 u13-2 j12-4 e3-6
    2 e6-34 u12-2


    So I found this script (at bottom) that somebody did for somebody else
    that sorts each row individually alphabetically and it doesn't touch
    column A which is what I need but every time I have to edit the script
    with how deep and how wide it is, is there a way so that after you run
    the script it will prompt you how deep and wide you want it. But also I
    still need it to seperate each column so no one has more than one
    starting letter. example after it is sorted it would look like this:

    ROW column B column C column D column E
    1 j1-2 j12-4 e3-6 u13-2
    2 e6-34 u12-2

    Some have 3 "j's" some have 3 "u's" its somewhat random. is there any
    way to do this. Right now with the script I found after it alphabatize
    them. I would then have to sort the columns alphabetically then
    manually go down the list and shift over the remaining columns to the
    first column with that letter. then resort and continue this tell there
    is no column with more then one starting letter (except the first). I
    would just do it manually except I have to do this a couple times and
    each one can get around 800-900 rows deep. Thanks in advance for the
    help.



    Sub SortRows()


    Dim r As Long
    Dim lrow As Long


    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    lrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row


    'Make the r = 3 whatever the first row of data you want to sort on is.
    'The Cells(r, 2) means your data starts in Col 2 or Col B - adjust as
    necessary
    'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide


    For r = 3 To lrow
    With Cells(r, 2).Resize(1, 4)
    .Sort Key1:=Cells(r, 1), Order1:=xlAscending, Header:=xlGuess,
    _
    Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
    End With
    Next r


    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic


    End Sub


  2. #2
    Tom Ogilvy
    Guest

    Re: need help righting script to sort rows alphabetically then seperate them to line up

    Sub SortRows()


    Dim r As Long
    Dim lrow As Long
    Dim col as Long, rStart as Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    rStart = Inputbox("Enter first row to sort")
    col = InputBox("Enter # of columns)
    lrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row


    'Make the r = 3 whatever the first row of data you want to sort on is.
    'The Cells(r, 2) means your data starts in Col 2 or Col B - adjust as
    necessary
    'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide


    For r = rStart To lrow
    With Cells(r, 2).Resize(1, col)
    .Sort Key1:=Cells(r, 1), Order1:=xlAscending, Header:=xlGuess, _
    Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
    End With
    Next r


    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic


    End Sub

    --
    Regars,
    Tom Ogilvy

    <Kamachi923@gmail.com> wrote in message
    news:1132017848.294220.6050@f14g2000cwb.googlegroups.com...
    > Hello, can anybody please help me I am new to script righting and was
    > trying to figure out how to sort my table.
    >
    > I have a table that is at least 800 rows and about 5 columns wide.
    > short example of a row would be
    >
    > ROW column B column C column D column E
    > 1 j1-2 u13-2 j12-4 e3-6
    > 2 e6-34 u12-2
    >
    >
    > So I found this script (at bottom) that somebody did for somebody else
    > that sorts each row individually alphabetically and it doesn't touch
    > column A which is what I need but every time I have to edit the script
    > with how deep and how wide it is, is there a way so that after you run
    > the script it will prompt you how deep and wide you want it. But also I
    > still need it to seperate each column so no one has more than one
    > starting letter. example after it is sorted it would look like this:
    >
    > ROW column B column C column D column E
    > 1 j1-2 j12-4 e3-6 u13-2
    > 2 e6-34 u12-2
    >
    > Some have 3 "j's" some have 3 "u's" its somewhat random. is there any
    > way to do this. Right now with the script I found after it alphabatize
    > them. I would then have to sort the columns alphabetically then
    > manually go down the list and shift over the remaining columns to the
    > first column with that letter. then resort and continue this tell there
    > is no column with more then one starting letter (except the first). I
    > would just do it manually except I have to do this a couple times and
    > each one can get around 800-900 rows deep. Thanks in advance for the
    > help.
    >
    >
    >
    > Sub SortRows()
    >
    >
    > Dim r As Long
    > Dim lrow As Long
    >
    >
    > Application.ScreenUpdating = False
    > Application.Calculation = xlCalculationManual
    > lrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    >
    >
    > 'Make the r = 3 whatever the first row of data you want to sort on is.
    > 'The Cells(r, 2) means your data starts in Col 2 or Col B - adjust as
    > necessary
    > 'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide
    >
    >
    > For r = 3 To lrow
    > With Cells(r, 2).Resize(1, 4)
    > .Sort Key1:=Cells(r, 1), Order1:=xlAscending, Header:=xlGuess,
    > _
    > Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
    > End With
    > Next r
    >
    >
    > Application.ScreenUpdating = True
    > Application.Calculation = xlCalculationAutomatic
    >
    >
    > End Sub
    >




  3. #3
    Kamachi923@gmail.com
    Guest

    Re: need help righting script to sort rows alphabetically then seperate them to line up

    Thanks that solved part of the now do you have any idea at how I would
    approach the other problem. Like I said I don't really know how to
    right script yet, but for this issue I don't know anyway of how to set
    this up. Do you have an idea of how to start ways I could approach
    this. Thanks again.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1