VBA How to Sort multiple Ranges of Data

[lepage0]

Good day,

I am looking for a solution to my issue. Cannot figure it out to save my life.

What I have is a long list of data but seperated by blank rows. For each Block of data (EX rows 4:8) needs to be sorted by Column "I" largest to smallest.

THere are 3-6 blocks of data. Again, all seperated by an empty row.

I have this code below that works perfectly with the sorting, however - What is hapening is that its actually skipping all the blank rows and jumps straight to literally the last row of all my data, sorting it, and bunching it all up. Which cannot happen. Needs to be separated. See code Below.

Sub Sort()


Dim Lastrow As Long

Lastrow = Cells(Rows.Count, 2).End(xlUp).Row
    Range("A4:K" & Lastrow).Sort key1:=Range("I4:I" & Lastrow), _
        order1:=xlDescending, Header:=xlNo

End Sub

What this code is doing is choosing all the Data from "A4" to "K and blank row" and then sorting Row "I" in Descending Order.


See example of Data - from the top row to where there is a blank row i need that chunk of data sorted by column "I"

A B C D E F G H I

158

7:13:54

4:53:37

2:45

1:52

62.9%

12:09:26

51.7%

2:45

1:52

7

:28:32

:30:20

4:05

4:20

2.8%

0:58:55

4.2%

4:05

4:20

1

:02:54

:03:52

2:54

3:52

0.4%

0:06:46

0.5%

2:54

3:52

2

:04:43

:03:38

2:22

1:49

0.8%

0:08:22

0.6%

2:22

1:49

4

:30:01

:23:27

7:30

5:52

1.6%

0:53:28

3.8%

7:30

5:52

1

:06:48

:04:21

6:48

4:21

0.4%

0:11:09

0.8%

6:48

4:21

3

:09:53

:04:51

3:18

1:37

1.2%

0:14:45

1.0%

3:18

1:37

3

:14:39

:07:42

4:53

2:34

1.2%

0:22:21

1.6%

4:53

2:34

5

:11:14

:10:14

2:15

2:03

2.0%

0:21:30

1.5%

2:15

2:03

1

:03:43

:01:11

3:43

1:11

0.4%

0:04:54

0.3%

3:43

1:11

1

:02:12

:04:17

2:12

4:17

0.4%

0:06:29

0.5%

2:12

4:17

1

:03:53

:06:25

3:53

6:25

0.4%

0:10:18

0.7%

3:53

6:25

13

:44:01

:49:45

3:23

3:50

5.2%

1:33:49

6.7%

3:23

3:50

1

:01:36

:02:34

1:36

2:34

0.4%

0:04:10

0.3%

1:36

2:34

1

:03:57

:07:18

3:57

7:18

0.4%

0:11:15

0.8%

3:57

7:18

1

:05:40

:00:34

5:40

0:34

0.4%

0:06:14

0.4%

5:40

0:34

2

:05:19

:03:42

2:40

1:51

0.8%

0:09:02

0.6%

2:40

1:51

21

1:19:13

2:10:34

3:46

6:13

8.4%

3:29:39

14.9%

3:46

6:13

7

:18:36

:19:01

2:39

2:43

2.8%

0:37:34

2.7%

2:39

2:43

1

:01:19

:00:05

1:19

0:05

0.4%

0:01:24

0.1%

1:19

0:05

8

:07:47

:05:21

0:58

0:40

3.2%

0:13:04

0.9%

0:58

0:40

1

:07:41

:01:50

7:41

1:50

0.4%

0:09:31

0.7%

7:41

1:50

1

:04:33

:01:56

4:33

1:56

0.4%

0:06:29

0.5%

4:33

1:56

5

:26:50

:07:50

5:22

1:34

2.0%

0:34:40

2.5%

5:22

1:34

1

:09:28

:00:55

9:28

0:55

0.4%

0:10:23

0.7%

9:28

0:55

1

:12:16

:01:55

12:16

1:55

0.4%

0:14:11

1.0%

12:16

1:55

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col span="3"><col span="2"></colgroup>

 

[Akuini]

to have 2 sort columns, Column B and column D.

Try:

Sub Andy_Mitchell_2()

Dim c As Range, f As Range

Application.ScreenUpdating = False
'"A2" means start at A2
Set f = Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeConstants)

For Each c In f.Areas
    With c.Resize(, 4) 'expand to col A:D, 4 is col D
        'sort descending by col 2, then  sort ascending by col 4
        .Sort Key1:=.Columns(2), Order1:=xlDescending, Key2:=.Columns(4), Order2:=xlAscending, Header:=xlNo
    End With
Next
Application.ScreenUpdating = True

End Sub

Before:

Book1
	A	B	C	D
1
2	Jeremiah	1	F	Mario
3	Giovanni	2	O	Adrien
4	Owen	1	Z	Manuel
5
6
7	Iker	1	U	Boone
8	Blaise	1	B	Zein
9	Ariel	5	W	Patrick
10	Rex	1	N	Kellen
11
12
13	Ruben	17	M	Clyde
14	Aydin	18	K	Mark
Sheet3

After:

Book1
	A	B	C	D
1
2	Giovanni	2	O	Adrien
3	Owen	1	Z	Manuel
4	Jeremiah	1	F	Mario
5
6
7	Ariel	5	W	Patrick
8	Iker	1	U	Boone
9	Rex	1	N	Kellen
10	Blaise	1	B	Zein
11
12
13	Aydin	18	K	Mark
14	Ruben	17	M	Clyde
Sheet3

 

[Andy Mitchell]

Fantastic, works perfectly, I have way better understanding how to manipulate my data now. Thank you for your time and helping me

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.