Macro, Rearrange rows, whilst locking some

[Pedrro]

Hi,

I have a list that when i push a button i would like the top row to move to the bottom and all subsequent rows to move up, except for those that are 'greyed' out. So if a row is 'greyed' out (cell is 'greyed' out if a drop down selection is equal to a value) i would like the row or rows below it to skip it and move up to the next position, effectivaly locking that grey row in its current positon. Rows can become 'greyed' out at any time by the selection in the drop down box being equal to either "call" or "leave" otherwise its blank and free to move. and by rows i mean cells A:C

Hopefully i have been clear enough¿

 

[Weaver]

Welcome to the board!

Just a thought, I'm picking up from your post that maybe the grey is the result of conditional formatting? If so, is it fair to say that it's the rows with 'call' or 'leave' in column C that need to be locked?

 

[Peter_SSs]

Welcome to the MrExcel board!

Test this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    <SPAN style="color:#00007F">Dim</SPAN> a, b<br>    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, u <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> BottomDone <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    r = Range("A" & Rows.Count).End(xlUp).Row<br>    u = r - 2<br>    a = Range("A3:C" & r).Value<br>    <SPAN style="color:#00007F">ReDim</SPAN> b(1 <SPAN style="color:#00007F">To</SPAN> u, 1 <SPAN style="color:#00007F">To</SPAN> 3)<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> u<br>        <SPAN style="color:#00007F">If</SPAN> a(i, 3) = vbNullString <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> BottomDone <SPAN style="color:#00007F">Then</SPAN><br>                k = u<br>                BottomDone = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            b(k, 1) = a(i, 1)<br>            b(k, 2) = a(i, 2)<br>            b(k, 3) = a(i, 3)<br>            k = i<br>        <SPAN style="color:#00007F">Else</SPAN><br>            b(i, 1) = a(i, 1)<br>            b(i, 2) = a(i, 2)<br>            b(i, 3) = a(i, 3)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    Range("A3:C" & r).Value = b<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

For the future, consider using one of the three methods in my signature block for posting a small screen shot directly in your post. There are two advantages. Potential helpers will not be put off by ..

1. Having to track to another website to view your sample, and

2. Having to manually type out the sample data to test because they cannot copy/paste from your screen shot.

 

[Pedrro]

Pete,
Thanks for youe help, Sorry about the screen dump, couldnt get the addin to work when i tried it.

Your solution works well, only problem i have is it clears the values in the first two cells when that row is moved to the bottom, i would like the data to stay in those cells.

Thanks for your quick reply and help

Pedrro

 

[Pedrro]

Not to worry Pete, I got it to work now, must have had some data in a cell right at the bottom of the sheet