0

I have a sheet with multiple tables on the same worksheet 'Sheet1' separated by a blank row.

I want the VBA to sort one by one.

Considerations

  • Each table has different amount of rows
  • In the future, the columns amount will also change

What will always be the same is:

  • The blank row between tables on column B, which is meant to be the starting point for excel to recognize there is a new table.
  • Each table will always be sorted by column C

To give you a better picture, I am looking at consumer data cross tabs, where:

COLUMN A is QUESTION TYPE

COLUMN B is ANSWER CHOICES

COLUMN C is Header is "TOTAL", and it contains % per answer choices

The rest of COLUMS follow % like C but with different headers like "male" "female" "user" "non user" "Age 18-25" "Age 26-34"....etc.

I want to create a looped macro that will sort each table (and all the columns within the table) by Column C in ascending order identifying that it has to search for the next blank space and the table will be below it.

I've tried looking at Looping, Sorting, and finding the next empty cell. But I can't put the pieces together for some reason.

Any help will be GREATLY appreciated!

ORIGINAL FILE

AFTER MACRO DESIRED OUTPUT

Previous code tried:

Dim oneArea as Range

For Each oneArea in Range("C:C").SpecialCells(xlCellTypeConstants).Area
    oneArea.EntireRow.Sort key1:=oneArea.Cells(1,1), order1:=xlAscending
Next oneArea

For ref: This one is the closest example and macro i've found to what im going for, but it doesn't apply to my scenario / couldn't make it work:

Macro to sort data until blank row(s), then repeat

Community
  • 1
  • 1
Tamichan
  • 3
  • 3
  • Can you post some sample data? (Copy/paste as a table) or at worst, a screen shot of the data, and your expected output? Also, what have you tried so far? Please post any code you have. – BruceWayne Jan 23 '17 at 00:29
  • Sure, I wasn't able to attach the excel, added some screenshots and codes. – Tamichan Jan 23 '17 at 01:01
  • For finding next table you can use `Selection.End(xlDown).Select` and when you find first cell of new table and select it you can use this for selecting area that you want to sort: `Selection.resize(Selection.Rows.Count,Selection.Columns.Count - 1).Offset(0,1).Select` – Egan Wolf Jan 23 '17 at 08:04

1 Answers1

0

The following code is looping through every row in your sheet, checking for begin and end of every table, and sort them. Enjoy.

Sub Mak1()
Dim LastRow As Long, LastCol As Long
Dim TabBeg As Long, TabEnd As Long

    With Sheets("Sheet1")
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).row
        LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
        TabBeg = 4
        TabEnd = 4
        For i = 4 To LastRow + 1
            If .Cells(i, 2).Value = "" Then
                With .Sort
                    .SortFields.Clear
                    .SortFields.Add Key:=Range(Cells(TabBeg, 3), Cells(TabEnd, 3)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                    .SetRange Range(Cells(TabBeg, 2), Cells(TabEnd, LastCol))
                    .Header = xlNo
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
                TabBeg = i + 1
                TabEnd = i
            Else
                TabEnd = TabEnd + 1
            End If
         Next i
    End With

End Sub
Limak
  • 1,511
  • 3
  • 12
  • 22