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]

Try this:
Assuming:
-There is header in row 1
-Last column is K

Sub a930196()
Dim i As Long
Dim y As Long
Dim rr As Long

Application.ScreenUpdating = False
rr = Range("A" & Rows.count).End(xlUp).row
For i = 2 To rr - 1
  y = Range(Cells(i, "A"), Cells(rr + 1, "A")).Find("", SearchOrder:=xlByRows, LookAt:=xlWhole, _
  SearchDirection:=xlNext).row
  Range(Cells(i, "A"), Cells(y - 1, "K")).Sort key1:=Columns("I"), order1:=xlDescending, Header:=xlNo
  i = y
Next i
Application.ScreenUpdating = True
End Sub

 

[Andy Mitchell]

Hi, this works really well, I need to use this but for it to only start from row 20 downwards, how do I change this code to start from row 20?
Many thanks
Andy

 

[Akuini]

I need to use this but for it to only start from row 20 downwards, how do I change this code to start from row 20?

Try changing this:

For i = 2 To rr - 1

to

For i = 20 To rr - 1

 

[Andy Mitchell]

Try changing this:

For i = 2 To rr - 1

to

For i = 20 To rr - 1

Legend, this worked a treat, thank you so much, I have one other want, not sure if possible. Would it be possible to have more than one blank line between the rows that have data? further, could the number of blank lines vary, ie Row 20-25 has data, followed by 3 blank rows, rows 29-32 has data, followed by 5 blank lines, followed by more data etc etc. Would it be possible to alter the code to cater for that scenario?? Really apprecaite your help with this.

 

[Akuini]

@Andy Mitchell
Here's an example:
1. Start at row 2
2. Data is in col A:D
3. Sort descending by col C

Please, read the comments on how to change the values as needed.

Sub Andy_Mitchell()

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 3, i.e col C
        .Sort key1:=.Columns(3), order1:=xlDescending, Header:=xlNo
    End With
Next
Application.ScreenUpdating = True

End Sub

BEFORE

Book2
	A	B	C	D
2	Jeremiah	1	F	Mario
3	Giovanni	2	O	Adrien
4	Owen	3	Z	Manuel
5
6	Marvin	5	J	Paxton
7	Clayton	6	X	Joe
8
9
10
11	Iker	10	U	Boone
12	Blaise	11	B	Zein
13	Ariel	12	W	Patrick
14
15
16	Rex	16	N	Kellen
17	Ruben	17	M	Clyde
18	Aydin	18	K	Mark
Sheet2

AFTER

Book2
	A	B	C	D
1
2	Owen	3	Z	Manuel
3	Giovanni	2	O	Adrien
4	Jeremiah	1	F	Mario
5
6	Clayton	6	X	Joe
7	Marvin	5	J	Paxton
8
9
10
11	Ariel	12	W	Patrick
12	Iker	10	U	Boone
13	Blaise	11	B	Zein
14
15
16	Rex	16	N	Kellen
17	Ruben	17	M	Clyde
18	Aydin	18	K	Mark
Sheet2

 

[Andy Mitchell]

@Andy Mitchell
Here's an example:
1. Start at row 2
2. Data is in col A:D
3. Sort descending by col C

Please, read the comments on how to change the values as needed.

Sub Andy_Mitchell()

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 3, i.e col C
        .Sort key1:=.Columns(3), order1:=xlDescending, Header:=xlNo
    End With
Next
Application.ScreenUpdating = True

End Sub

BEFORE

Book2
	A	B	C	D
2	Jeremiah	1	F	Mario
3	Giovanni	2	O	Adrien
4	Owen	3	Z	Manuel
5
6	Marvin	5	J	Paxton
7	Clayton	6	X	Joe
8
9
10
11	Iker	10	U	Boone
12	Blaise	11	B	Zein
13	Ariel	12	W	Patrick
14
15
16	Rex	16	N	Kellen
17	Ruben	17	M	Clyde
18	Aydin	18	K	Mark
Sheet2

AFTER

Book2
	A	B	C	D
1
2	Owen	3	Z	Manuel
3	Giovanni	2	O	Adrien
4	Jeremiah	1	F	Mario
5
6	Clayton	6	X	Joe
7	Marvin	5	J	Paxton
8
9
10
11	Ariel	12	W	Patrick
12	Iker	10	U	Boone
13	Blaise	11	B	Zein
14
15
16	Rex	16	N	Kellen
17	Ruben	17	M	Clyde
18	Aydin	18	K	Mark
Sheet2

Looks like just what I need, thank you again, one final request, I now know which row to get it to start on, could you tell me where to change it so it only works from row 20 to row 99, anything below row 99 it wont touch. This thread I'm sure will help lots of others as well. Thanks again and is certainly helping me with my vb education.

 

[Akuini]

could you tell me where to change it so it only works from row 20 to row 99, anything below row 99 it wont touch

Try changing this:

Set f = Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeConstants)

to:

Set f = Range("A20:A99").SpecialCells(xlCellTypeConstants)

And one thing I should mention earlier:
I assume we can use col A to determine if a row is empty, that is, if a cell in col A is empty then the entire row must be empty.

 

[Andy Mitchell]

Try changing this:

Set f = Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeConstants)

to:

Set f = Range("A20:A99").SpecialCells(xlCellTypeConstants)

And one thing I should mention earlier:
I assume we can use col A to determine if a row is empty, that is, if a cell in col A is empty then the entire row must be empty.

Perfect, perfect, perfect, thank you so much, all good, I have learnt a lot from this one thread, all the best

 

[Andy Mitchell]

Tested and all works, I think what would complete this thread would be able to have 2 sort columns, Column B and column D. Column B could have multiple 1's for example and then in column D you might want to sort names alphabetically. What code would need changing to be able to do more than one sort column? Many thanks