Excel Code Help

H

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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
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
Rick Rothstein said:
Why are names 5, 7 and 8 located where they are in the output table?
Click to expand...

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
MickG said:
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
Click to expand...

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
Fluff said:
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
Click to expand...

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
Fluff said:
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
Click to expand...

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
You must log in or register to reply here.

Similar threads

A
  • Solved
VBA Case Error
Replies
2
Views
65
Ark68
A
H
  • Solved
Create list of last(family) names when parents have children in more than 1 class
Replies
7
Views
172
Peter_SSs
Peter_SSs
Joneye
  • Question Question
Index & Match or Other Look up Advice
Replies
2
Views
67
Joneye
Joneye
WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
117
RoryA
RoryA
R
  • Question Question
VBA Generate email with multiple data in body.
Replies
2
Views
129
Logit
Logit

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

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