Excel Code Help

hazmat

New Member
Joined
Jun 14, 2019
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
Totally new to excel code and such. I've been able to fumble my way through the rest of the code on my spreadsheet, but have one more function of my sheet that i would like, but don't even know where to start, or if even possible.


i would like to be able to press the a control button and have the names in A1 thru A9 reorganize, such that the name in A1 will change to the name that is in the row that has the first blank after the last number in columns C, E, and G.
A2 thru A9 will then follow with the next name in line and wrap around.
The last number may be in any column, C, E or G.
Numbers are actually going to be dates (if that even matters)


So, in the following example, cell G3 has the last number. So, name 4 (the name in the first row after G3), should be placed the Cell A1, and the rest of the names should follow like this: name 5 thru 9, then name 1 thru 3.

From this......

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]name 1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]name 2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]name 3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]name 4[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]name 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]name 6[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]name 7[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]name 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]name 9[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

.....to this

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]name 4[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]name 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]name 6[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]name 7[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]name 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]name 9[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]name 1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]name 2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]name 3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

hope this makes sense, and thanks in advance for any help.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Perhaps:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG16Jun40
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 Rw = Range("C1:H9").Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row
ReDim ray(1 To 9, 1 To 7)
    [COLOR="Navy"]For[/COLOR] n = Rw + 1 To 9
        c = c + 1
        [COLOR="Navy"]For[/COLOR] Ac = 1 To 7
            ray(c, Ac) = Cells(n, Ac)
        [COLOR="Navy"]Next[/COLOR] Ac
    [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]For[/COLOR] n = 1 To Rw
        c = c + 1
        [COLOR="Navy"]For[/COLOR] Ac = 1 To 7
            ray(c, Ac) = Cells(n, Ac)
        [COLOR="Navy"]Next[/COLOR] Ac
    [COLOR="Navy"]Next[/COLOR] n
Range("A1").Resize(9, 7) = ray
Regards Mick
 
Upvote 0
Another option
Code:
Sub hazmat()
   Dim Rw As Long
   
   Rw = Application.Min(Range("C" & Rows.Count).End(xlUp).Row, _
                        Range("E" & Rows.Count).End(xlUp).Row, _
                        Range("G" & Rows.Count).End(xlUp).Row)
   With Rows("1:" & Rw)
      .Copy Range("A" & Rows.Count).End(xlUp).Offset(1)
      .Delete
   End With
End Sub
 
Upvote 0
Why are names 5, 7 and 8 located where they are in the output table?

because i need the names to stay in order and just change which name starts at the top of the list.
After the re-sort, the data (numbers) in columns c, e and g become irrelevant, because they will be cleared, but i need them there to determine which name gets put at the top.
 
Upvote 0
Perhaps:-
Code:
[COLOR=Navy]Sub[/COLOR] MG16Jun40
[COLOR=Navy]Dim[/COLOR] Rw [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
 Rw = Range("C1:H9").Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row
ReDim ray(1 To 9, 1 To 7)
    [COLOR=Navy]For[/COLOR] n = Rw + 1 To 9
        c = c + 1
        [COLOR=Navy]For[/COLOR] Ac = 1 To 7
            ray(c, Ac) = Cells(n, Ac)
        [COLOR=Navy]Next[/COLOR] Ac
    [COLOR=Navy]Next[/COLOR] n
    [COLOR=Navy]For[/COLOR] n = 1 To Rw
        c = c + 1
        [COLOR=Navy]For[/COLOR] Ac = 1 To 7
            ray(c, Ac) = Cells(n, Ac)
        [COLOR=Navy]Next[/COLOR] Ac
    [COLOR=Navy]Next[/COLOR] n
Range("A1").Resize(9, 7) = ray
Regards Mick

Thanks Mick, this seems to be working how i want.
I just had to change, in the 3rd line, ("C1:H9") to ("C1:C9,E1:E9,G1:G9") because i don't want it to consider Columns D, F or H. (i think i did that correctly)

Now I just have to figure out how to apply this to my spreadsheet where there will be 30 names and 5 columns, with different cells being used.
Then try to understand how that code actually works.

Thanks again
 
Upvote 0
Another option
Code:
Sub hazmat()
   Dim Rw As Long
   
   Rw = Application.Min(Range("C" & Rows.Count).End(xlUp).Row, _
                        Range("E" & Rows.Count).End(xlUp).Row, _
                        Range("G" & Rows.Count).End(xlUp).Row)
   With Rows("1:" & Rw)
      .Copy Range("A" & Rows.Count).End(xlUp).Offset(1)
      .Delete
   End With
End Sub

Thanks for your reply.
This code almost works how i want, but if there's nothing in Column G it doesn't. And thats a condition that may happen.
Also, for some reason when i run your code with a control button, the button keeps moving up the page a little every time i press it.
thanks
 
Upvote 0
Can you have a situation like this


Excel 2013/2016
ABCDEFG
1name 1123
2name 21
3name 3123
4name 413
5name 53
6name 61
7name 71
8name 8
9name 91
22


where you need to move rows 1:3 to the end based on col E
 
Upvote 0
Can you have a situation like this

Excel 2013/2016
ABCDEFG
name 1
name 2
name 3
name 4
name 5
name 6
name 7
name 8
name 9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
22



where you need to move rows 1:3 to the end based on col E

No. For any given row, G cannot have a number unless both C & E have a number. And E can't have a number unless C does.
It is possible, however, that Column G has no data in it at all. In that case, The re-sort would be based on the last number in column E, or if no numbers in either E or G, based on Column C
 
Upvote 0
In that case best use MickG's code.
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,186
Members
452,615
Latest member
bogeys2birdies

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