4

I'm trying to sort a large number of lists from A to Z. But if I sort column A, I don't want all other columns to be rearranged. I need all these lists to indivually be sorts alphabetically. I know it's possible to do one by one but I have 278 columns. Is there a way do automate it?

Community
  • 1
  • 1
Batman
  • 5,563
  • 18
  • 79
  • 155

2 Answers2

4

If there are no blank cells in any of the columns' data then the following is a slightly different approach which doesn't assume a maximum for the number of rows.

Sub SortIndividualJR()
    Dim rngFirstRow As Range
    Dim rng As Range
    Dim ws As Worksheet

    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    Set rngFirstRow = ws.Range("A1:JR1")
    For Each rng In rngFirstRow
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=rng, Order:=xlAscending
            'assuming there are no blank cells..
            .SetRange ws.Range(rng, rng.End(xlDown))
            .Header = xlYes
            .MatchCase = False
            .Apply
        End With
    Next rng
    Application.ScreenUpdating = True
End Sub

There is nothing wrong with user1281385's code; as I say, this is just an alternative.

Added If there are some blanks then modify the above code to use .SetRange ws.Range(rng, rng.Range("A1000").End(xlUp)), changing 1000 to whatever you expect will be greater than the maximum number of data-rows.

Andy G
  • 19,232
  • 5
  • 47
  • 69
  • It seems like there are some blanks so it ends up looking like this: http://i.imgur.com/FItNV7M.png Do you know how I could clean that up? – Batman Jun 21 '13 at 19:25
  • Then stick with user1281385's method (once the spaces and newline are corrected). Alternatively, my method could be changed slightly: modify to `.SetRange ws.Range(rng, rng.Range("A1000").End(xlUp))`. Change A1000 to whatever you expect will be greater than the maximum number of rows. – Andy G Jun 21 '13 at 19:50
  • I tried running the above code with the change for the blanks and changed A1000 to A100. But it doens't change anything. There gaps actually got worst and applied to more columns than before. – Batman Jun 24 '13 at 13:41
  • The number should be *more* than the possible maximum number of rows. Do you have less than 100 rows? Otherwise, I am not able to replicate your issue,sorry. – Andy G Jun 24 '13 at 13:53
  • The largest row is around 86 but maybe it's because of the blanks? I'll try again with a larger number – Batman Jun 24 '13 at 14:03
  • Hmm yea I tried it again with 500 and it's the same thing. This is what it looks like: http://i.imgur.com/d5q1UDj.png – Batman Jun 24 '13 at 14:04
  • I can only guess that you have a lot of cells that are not empty, perhaps contain a space. Click into the top cell of one of the columns that is misbehaving and press Ctrl-Down a few times. – Andy G Jun 24 '13 at 14:15
  • Yea I see what you mean. There are huge empty gaps for some reason. Do you know if there's a way to clean that up? – Batman Jun 24 '13 at 14:19
  • If there are no spaces other than in these cells then highlight the whole area and Replace a space with nothing. If all the other cells are numbers then you could use Ctrl-G, Special, Constants, tick only Text, which will select all the text-cells, then press Delete. Otherwise, with the lists sorted using my procedure you'll need to go through each column deleting the blocks of spaces. Autofiltering might help you to delete them. A procedure could also be written to go through the data. – Andy G Jun 24 '13 at 14:30
  • Hi, just wanted to let you know that I was able to find some VBA to clear all empty cells. Once those were out of the way your code worked perfectly. Thank you. – Batman Jun 24 '13 at 20:44
  • @Batman. No problem. Thanks for coming back. – Andy G Jun 24 '13 at 20:45
  • I was wondering if you know how I could modify the code you gave me here to additionally sort by cell color? Your code works perfectly, I'm just trying to add an additional task but I'm having some trouble with it. http://stackoverflow.com/questions/17530785/sorting-alphabetically-and-by-cell-color?noredirect=1#comment25493903_17530785 – Batman Jul 08 '13 at 16:13
2

Select the column you want to sort then press "sort and filter" a box will come up saying 

Expand selection
continue with current selection

Choose continue with current selection

Then choose how you want it sorted. Repeat for each column you want to sort.

Expand selection is the default option

Macro

Dim oneRange as Range 
Dim aCell as Range 
For I = 1 to 278
    Set oneRange = Range("r1c" & I & ":r1000c" & I) 
    Set aCell = Range("r1c" & I)
    oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes
Next I

Should work

Andy G
  • 19,232
  • 5
  • 47
  • 69
exussum
  • 18,275
  • 8
  • 32
  • 65
  • Is there a way to do this for multiple columns at once because I have 278 columns. – Batman Jun 21 '13 at 18:06
  • You can't do this all at once. You could record a macro, doing this for the first column. Then modify the macro, using a loop to repeat the process for all of the other columns. – Andy G Jun 21 '13 at 18:13
  • the last two lines before Next I give me an error. – Batman Jun 21 '13 at 19:27
  • Add some spaces in. I'm on a phone typing. Spaces around the & should be ok – exussum Jun 21 '13 at 19:44
  • Yes, spaces around the & are necessary, otherwise they can be misinterpreted as the Type Declaration Character (for Long). – Andy G Jun 21 '13 at 19:55
  • I ran it and got Run-Time Error "1004" Method Range of object _Global Failed. – Batman Jun 24 '13 at 13:43
  • run it while the focus is on a sheet. – exussum Jun 24 '13 at 13:45
  • On the sheet? I selected the entire sheet by click in the top left corner and still got the error. I also did control A and same thing. – Batman Jun 24 '13 at 13:49