Excel Code Help

[hazmat]

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

	A	B	C	D	E	F	G	H	I
1	name 1		1		2		3
2	name 2		1
3	name 3		1		2		3
4	name 4		1
5	name 5
6	name 6		1		2
7	name 7		1
8	name 8
9	name 9		1		2

<tbody>
</tbody>

.....to this

	A	B	C	D	E	F	G	H	I
1	name 4		1
2	name 5
3	name 6		1		2
4	name 7		1
5	name 8
6	name 9		1		2
7	name 1		1		2		3
8	name 2		1
9	name 3		1		2		3

<tbody>
</tbody>

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

 

[Rick Rothstein]

Why are names 5, 7 and 8 located where they are in the output table?

 

[MickG]

Perhaps:-

[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

 

[Fluff]

Another option

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

 

[hazmat]

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.

 

[hazmat]

Perhaps:-

[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

 

[hazmat]

Another option

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

 

[Fluff]

Can you have a situation like this

Excel 2013/2016
	A	B	C	D	E	F	G
1	name 1		1		2		3
2	name 2		1
3	name 3		1		2		3
4	name 4		1				3
5	name 5						3
6	name 6		1
7	name 7		1
8	name 8
9	name 9		1
22

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

 

[hazmat]

Can you have a situation like this

Excel 2013/2016

	A	B	C	D	E	F	G
1	name 1		1		2		3
2	name 2		1
3	name 3		1		2		3
4	name 4		1				3
5	name 5						3
6	name 6		1
7	name 7		1
8	name 8
9	name 9		1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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

 

[Fluff]

In that case best use MickG's code.