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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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

S
  • Question Question
I am trying write a formula for when I enter in the machine number the machine name populates with the correct machine name
Replies
2
Views
128
Joe4
Joe4
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
357
Anthony47
A
GMC The Macro Man
  • Solved
SUM COUNTIFS with AND + OR
Replies
3
Views
113
Fluff
Fluff
Z
  • Solved
Look up to return name
Replies
4
Views
73
Peter_SSs
Peter_SSs
M
  • Question Question
I need help with VBA/macro script to collect user input and fill cells
Replies
4
Views
461
mumps
mumps

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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