VBA sort each dataset without removing blank rows

komhs

New Member
Joined
Jun 12, 2016
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
Hi to all and thanks in advance for the help...
I am trying to create a macro which will do the following in my sheet: I want to sort (each time with a column of my choice), each one of the small data sets, which are located between empty lines of a large data set.
For example:
I want to sort by column E (smallest to larger) the entire Data Set from A2:I16, but I want sorting to be made individually for each one of the following data sets (A3:I6, A8:I11, A14:I17) and by keeping exactly where they are the blank rows (in this example 2, 7, 12, 18) as well.
As you can see in the second picture, the data set has been set in alphabetical order in each one of the -between the blank rows- data sets.
Any code (not necessarily exclusively based on this example, because I have different datasets in different sheets) would be helpful :)
Thanks again
 

Attachments

  • Capture.JPG
    Capture.JPG
    176.4 KB · Views: 16
  • Capture1.JPG
    Capture1.JPG
    160.8 KB · Views: 17

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
@komhs
Try this:
VBA Code:
Sub a1185638a()
Dim i As Long
Application.ScreenUpdating = True
ActiveSheet.AutoFilterMode = False
With ActiveSheet.UsedRange.Resize(, 9)
    .AutoFilter Field:=5, Criteria1:="*"
    With .SpecialCells(xlCellTypeVisible)
        For i = 2 To .Areas.Count
'        Debug.Print .Areas(i).Address
        .Areas(i).Sort Key1:=.Areas(i).Cells(1, 5), Order1:=xlAscending, Header:=xlNo
        Next
    End With
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
@komhs
Try this:
VBA Code:
Sub a1185638a()
Dim i As Long
Application.ScreenUpdating = True
ActiveSheet.AutoFilterMode = False
With ActiveSheet.UsedRange.Resize(, 9)
    .AutoFilter Field:=5, Criteria1:="*"
    With .SpecialCells(xlCellTypeVisible)
        For i = 2 To .Areas.Count
'        Debug.Print .Areas(i).Address
        .Areas(i).Sort Key1:=.Areas(i).Cells(1, 5), Order1:=xlAscending, Header:=xlNo
        Next
    End With
End With
Application.ScreenUpdating = True
End Sub

So. when I want to change the column, I change the number in Field and the number in cells accordingly? Which means that if I want to sort by Column 9, I will do as in the picture?

Thanks in advance
 

Attachments

  • Capture3.JPG
    Capture3.JPG
    208.9 KB · Views: 11
Upvote 0
First of all, I made a mistake, actually Criteria1:="*" should be Criteria1:="<>" and also I forgot to reset the autofilter. So use this one instead.
VBA Code:
Sub a1185638c()
Dim i As Long
Application.ScreenUpdating = True
ActiveSheet.AutoFilterMode = False
With ActiveSheet.UsedRange.Resize(, 9)
    .AutoFilter Field:=5, Criteria1:="<>"
    With .SpecialCells(xlCellTypeVisible)
        For i = 2 To .Areas.Count
'        Debug.Print .Areas(i).Address
        .Areas(i).Sort Key1:=.Areas(i).Cells(1, 5), Order1:=xlAscending, Header:=xlNo
        Next
    End With
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

So. when I want to change the column, I change the number in Field and the number in cells accordingly? Which means that if I want to sort by Column 9, I will do as in the picture?
Yes. But you don't need to change this part Field:=5, base on your example, you can use Field 1 to 9, the result are the same.
 
Upvote 0
Solution
You're welcome, glad to help & thanks for the feedback.:)
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top