# Rearrange rows in matrix (Excel)

#### Pax2Garo

##### New Member
Hi everyone,

I'm actually struggling with excel in order to perform a certain command.

I have a very basic matrix consisting of rows and columns. What I'd like to do is to rearrange (changing the location of) the rows in the matrix based on a certain criteria.
What I'd like to accomplish is: If one value (cell) in an entire row of the matrix is higher than a certain value (a cut-off value) then the entire row should be copy/paste to another location in excel in order to gather all the rows with values higher than the specified value on one side and the rows with values lower than the specified value on the other side. It shouldn't affect the columns, only the rows should be switched.

Can someone help me with this? Maybe with a function or in VBA?

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### JLGWhiz

##### Well-known Member
Assuming it makes no differdence which is on top. Let's say the cut-off value is 10. The code below assumes no headers
Code:
``````Sub CutOffValue()
Dim sh As Worksheet, lr As Long, i As Long
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lr
If Application.Max(sh.Rows(i)) <= 10 Then
If sh.Cells(lr + 2, 1) = "" Then
sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(3)
Else
sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
End If
End If
Next
For j = 1 To lr
If Application.Max(sh.Rows(j)) > 10 Then
sh.Rows(j).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
End If
Next
End Sub``````
This would put the new matrix beneath the current matrix.

#### Pax2Garo

##### New Member
Hi,
I'm very thankful for your work, the code works perfectly.

I have a last question. Assume that the cut-off value is not a single number anymore but an interval. For example if a value in a row is higher than 10 or lower than -10 the entire row should be copy/paste to an other location and gathered together with the other rows fulfilling that criteria. So, it is exactly the same question as before but with a cut-off value of 10 and -10. How should I recode that into VBA? Just by changing numbers into the code?

Cheers!

#### JLGWhiz

##### Well-known Member
the parameters are controlled by the part in red font.
Code:
``````Sub CutOffValue()
Dim sh As Worksheet, lr As Long, i As Long
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lr
If Application.Max(sh.Rows(i)) [COLOR="#FF0000"]< -10 [/COLOR]Then
If sh.Cells(lr + 2, 1) = "" Then
sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(3)
Else
sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
End If
End If
Next
For j = 1 To lr
If Application.Max(sh.Rows(j)) [COLOR="#FF0000"]> 10 [/COLOR]Then
sh.Rows(j).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
End If
Next
End Sub``````
This code would use the interval +10 to -10

#### Pax2Garo

##### New Member

Hi

The last lines of code you provided doesn't work. Actually it should do the same than the FIRST code lines you provided but it doesn't. When I run the macro based on the FIRST code you provided, the matrix is rearranged perfectly based on the cut-off value. With the new one, some rows are copied but not the whole matrix and also not based on the good criteria. So the FIRST VBA code you provided was perfect, the only thing that should be changed is that if a value in a row is higher than 10 or lower than -10 the entire row should be copy/paste to an other location and gathered together with the other rows fulfilling that criteria and rows with a value (or values) between 10 and -10 should also be copy/paste to another location and gathered together.
Hopefully I'm clear enough with that.

#### JLGWhiz

##### Well-known Member
Yes, for the negative value it should have been a Min function instead of Max. However, after correcting that, I realized that because there are varying values in each row, it would be possible for the same row to meet the Max and Min criteria, but in different columns. Don't know if that is what you want or not. I also, noted that the wording in your last post seems to indicate that you want only the values between +10 and -10, which is a change from the original request. So please post an examplae of what you really want.

Code:
``````Sub CutOffValue()
Dim sh As Worksheet, lr As Long, i As Long
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lr
If Application.Min(sh.Rows(i)) < -10 Then
If sh.Cells(lr + 2, 1) = "" Then
sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(3)
Else
sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
End If
End If
Next
For j = 1 To lr
If Application.Max(sh.Rows(j)) > 10 Then
sh.Rows(j).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
End If
Next
End Sub``````

#### Pax2Garo

##### New Member

Hi JLGWhiz,

Here's an example of what I'd like to achieve:

Original Matrix

 1 2 3 4 12 13 14 15 -11 3 2 9 -9 -8 -7 -6 5 6 8 7 -20 -1 3 9

<tbody>
</tbody>

Rearranged Rows and Matrices

Matrix witth values between -10 and +10

 1 2 3 4 -9 -8 -7 -6 5 6 8 7

<tbody>
</tbody>

Matrix with values < -10 or > 10

 12 13 14 15 -11 3 2 9 -20 -1 3 9

<tbody>
</tbody>

As you can see, once a row has a value (a cell) with a number <-10 or > 10 it is redirected to the "Matrix with values <-10 or > 10".
In the case that a row doesn't have any value (cell) < -10 or > 10 then it will be redirected to the "Matrix with values between -10 and +10".
For this purpose I'd like to have a Macro, because I've to work with a very large number of data. So a macro will safe me a lot of time!

#### JLGWhiz

##### Well-known Member
Try this
Code:
``````Sub CutOffValue()
Dim sh As Worksheet, lr As Long, i As Long
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lr
If Application.Min(sh.Rows(i)) > -10 And Application.Max(sh.Rows(i)) < 10 Then
If sh.Cells(lr + 2, 1) = "" Then
sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(3)
Else
sh.Rows(i).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
End If
Next
For j = 1 To lr
If Application.Min(sh.Rows(i)) <= -10 Or Application.Max(sh.Rows(j)) >= 10 Then
sh.Rows(j).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
End If
Next
End Sub``````

#### Pax2Garo

##### New Member
It seems to work for part of the matrix.

My original matrix is this one:

 1 2 3 4 12 13 14 15 -11 3 2 9 -9 -8 -7 -6 5 6 8 7 -20 -1 3 9

<tbody>
</tbody>

When I run the macro, the result is the following one:

 1 2 3 4 -9 -8 -7 -6 5 6 8 7 12 13 14 15

<!--StartFragment--> <colgroup><col width="65" span="4" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

So two rows are missing :s

#### JLGWhiz

##### Well-known Member
There are no rows missing. It simply did not copy those two rows because neither fits the criteria. In both the rows you have a value which exceed 10/-10 and value between 10/-10. The code could not include those rows in the criteria because they are exclusive of both the conditions you established. If you want to play with this further, I suggest you start a new thread.

Last edited:

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,654
Messages
5,854,965
Members
431,689
Latest member
jacker01

### 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.

### Which adblocker are you using?

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

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