Arranging data in a spreadsheet using VBA

A

ashukla1

New Member
Joined
Oct 31, 2017
Messages
7
I Have a data in aspreadsheet that is spread horizantally see below.

[TABLE="class: grid, width: 500"]
<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="width:48pt" width="64" span="3"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:3913;width:80pt" width="107"> <col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> <col style="width:48pt" width="64" span="7"> </colgroup><tbody>[TR]
[TD="width: 64"]Name
[/TD]
[TD="width: 68"]Date
[/TD]
[TD="width: 64"]ID
[/TD]
[TD="width: 78"]Description
[/TD]
[TD="width: 64"]Base[/TD]
[TD="width: 64"]Fees
[/TD]
[TD="width: 64"]Total
[/TD]
[TD="width: 78"]Description
[/TD]
[TD="width: 107"]Base
[/TD]
[TD="width: 64"]Fees
[/TD]
[TD="width: 64"]Total
[/TD]
[TD="width: 78"]Description
[/TD]
[TD="width: 64"]Base
[/TD]
[TD="width: 64"]Fees
[/TD]
[TD="width: 64"]Total[/TD]
[TD="width: 64"]Description[/TD]
[TD="width: 64"]Base
[/TD]
[TD="width: 64"]Fees
[/TD]
[TD="width: 64"]Total
[/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD="align: right"]11/2/2017[/TD]
[TD="align: right"]123456[/TD]
[TD]Abc[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD]DEF[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD]JKL[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]60
[/TD]
[TD]XYZ
[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]654321[/TD]
[TD]Abc[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD]DEF[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD]JKL[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I need this data to be arranged Vertically see example below.

[TABLE="class: grid, width: 466"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Name
[/TD]
[TD]Date[/TD]
[TD]ID[/TD]
[TD]Description[/TD]
[TD]Base[/TD]
[TD]Fees [/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]11/2/2017[/TD]
[TD="align: right"]123456[/TD]
[TD]Abc[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]11/2/2017[/TD]
[TD="align: right"]123456[/TD]
[TD]DEF[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]11/2/2017[/TD]
[TD="align: right"]123456[/TD]
[TD]JKL[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]60
[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]11/2/2017[/TD]
[TD="align: right"]123456[/TD]
[TD]XYZ[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]654321[/TD]
[TD]Abc[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]654321[/TD]
[TD]Abc[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]654321[/TD]
[TD]DEF[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]654321[/TD]
[TD]JKL[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]60[/TD]
[/TR]
</tbody>[/TABLE]


any help with this will be appreciated i have been trying to move this for days but no success.

to do this using a formula is a very long procedure and i have to do this every day.

Please 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.
Sort by date Sort by votes
Re: Help With Arranging data in a spreadsheet using VBA

Welcome to the board.

What is the name of the sheet the data is on?
What is the range address the data is in?
What cell do you want the re-arranged data to start in? If it's not the same sheet, please provide sheet name also.
 
Upvote 0
Re: Help With Arranging data in a spreadsheet using VBA

JackDanIce said:
Welcome to the board.

What is the name of the sheet the data is on?
What is the range address the data is in?
What cell do you want the re-arranged data to start in? If it's not the same sheet, please provide sheet name also.
Click to expand...



Hi JackDanice.


Thank you so much for replying
Data range is A1:DJ2380
My Data is on Sheet 1
I need the data rearranged in sheet 2
 
Upvote 0
Re: Help With Arranging data in a spreadsheet using VBA

Try this:-
NB:- To get sets of 4 [TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD="width: 78"]Description[/TD]
[TD="width: 64"]Base[/TD]
[TD="width: 64"]Fees [/TD]
[TD="width: 64"]Total[/TD]
[/TR]
</tbody>[/TABLE]
from column "D" on, you would need to have your last column as "DK" not "DJ".
On that basis this code should work.
Results on Sheet2.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG02Nov58
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Cells(1).CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * Int(UBound(Ray, 2) / 4), 1 To 7)
nray(1, 1) = "Name": nray(1, 2) = "Date": nray(1, 3) = "ID": nray(1, 4) = "Description"
nray(1, 5) = "Base": nray(1, 5) = "Fees": nray(1, 7) = "Total"
c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]For[/COLOR] Ac = 4 To UBound(Ray, 2) [COLOR="Navy"]Step[/COLOR] 4
      [COLOR="Navy"]If[/COLOR] Ray(n, Ac) <> "" [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            nray(c, 1) = Ray(n, 1)
            nray(c, 2) = Ray(n, 2)
            nray(c, 3) = Ray(n, 3)
            nray(c, 4) = Ray(n, Ac)
            nray(c, 5) = Ray(n, Ac + 1)
            nray(c, 6) = Ray(n, Ac + 2)
            nray(c, 7) = Ray(n, Ac + 3)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 7)
    .Value = nray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Re: Help With Arranging data in a spreadsheet using VBA

Hi Mick,

this did work great except that when i tried to add a few more columns to it it did not work.
i still need the keep moving the last four cells but i have added few more columns before them.
below is the code i used.
i am getting an error Subscript out of range.

Below is my data.

[TABLE="class: grid, width: 2722"]
<colgroup><col><col><col><col span="4"><col><col span="32"></colgroup><tbody>[TR]
[TD]Date
[/TD]
[TD]Report Type[/TD]
[TD]Applicant ID[/TD]
[TD]Applicant Name[/TD]
[TD]Package Name[/TD]
[TD]Reference Code[/TD]
[TD]Billing Code[/TD]
[TD]Location[/TD]
[TD]Run By[/TD]
[TD]Base[/TD]
[TD]Fees[/TD]
[TD]Total[/TD]
[TD]Component 1[/TD]
[TD]Component 1 Base[/TD]
[TD]Component 1 Fee[/TD]
[TD]Component 1 Total[/TD]
[TD]Component 2[/TD]
[TD]Component 2 Base[/TD]
[TD]Component 2 Fee[/TD]
[TD]Component 2 Total[/TD]
[TD]Component 3[/TD]
[TD]Component 3 Base[/TD]
[TD]Component 3 Fee[/TD]
[TD]Component 3 Total[/TD]
[TD]Component 4[/TD]
[TD]Component 4 Base[/TD]
[TD]Component 4 Fee[/TD]
[TD]Component 4 Total[/TD]
[TD]Component 5[/TD]
[TD]Component 5 Base[/TD]
[TD]Component 5 Fee[/TD]
[TD]Component 5 Total[/TD]
[TD]Component 6[/TD]
[TD]Component 6 Base[/TD]
[TD]Component 6 Fee[/TD]
[TD]Component 6 Total[/TD]
[TD]Component 7[/TD]
[TD]Component 7 Base[/TD]
[TD]Component 7 Fee[/TD]
[TD]Component 7 Total[/TD]
[/TR]
[TR]
[TD="align: right"]5/9/2017 17:53[/TD]
[TD]Applicant Report[/TD]
[TD="align: right"]105528354[/TD]
[TD]Joshua Napoleon Randle[/TD]
[TD]Package 1: Pre Hire Full BGC[/TD]
[TD] [/TD]
[TD]SGB - Shiftgig Bullpen, Inc.[/TD]
[TD]Milwaukee[/TD]
[TD]dsherman@shiftgig.com[/TD]
[TD="align: right"]$19.25[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$19.25[/TD]
[TD]SSN Trace[/TD]
[TD="align: right"]$1.25[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$1.25[/TD]
[TD]Client Criteria[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$1.00[/TD]
[TD]Multi-State Instant Criminal Check[/TD]
[TD="align: right"]$3.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$3.00[/TD]
[TD]Criminal Check by Jurisdiction - State: WI, County: Milwaukee[/TD]
[TD="align: right"]$14.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$14.00[/TD]
[TD]Nationwide Sex Offender Registry Check[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD]Criminal Check by County - State: VA, County: Smyth[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5/15/2017 15:40[/TD]
[TD]Applicant Report[/TD]
[TD="align: right"]105883938[/TD]
[TD]Harmony Edwinta Lewis[/TD]
[TD]Package 1: Pre Hire Full BGC[/TD]
[TD] [/TD]
[TD]SGB - Shiftgig Bullpen, Inc.[/TD]
[TD]Memphis[/TD]
[TD]jhashmi@shiftgig.com[/TD]
[TD="align: right"]$19.25[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$19.25[/TD]
[TD]SSN Trace[/TD]
[TD="align: right"]$1.25[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$1.25[/TD]
[TD]Client Criteria[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$1.00[/TD]
[TD]Multi-State Instant Criminal Check[/TD]
[TD="align: right"]$3.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$3.00[/TD]
[TD]Criminal Check by Jurisdiction - State: TN, County: Shelby[/TD]
[TD="align: right"]$14.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$14.00[/TD]
[TD]Nationwide Sex Offender Registry Check[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD]Criminal Check by County - State: VA, County: Smyth[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5/22/2017 4:21[/TD]
[TD]Applicant Report[/TD]
[TD="align: right"]106297852[/TD]
[TD]Jose Israel Garcia[/TD]
[TD]Package 1: Pre Hire Full BGC[/TD]
[TD] [/TD]
[TD]SGB - Shiftgig Bullpen, Inc.[/TD]
[TD]Nashville[/TD]
[TD]awigand@shiftgig.com[/TD]
[TD="align: right"]$19.25[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$19.25[/TD]
[TD]SSN Trace[/TD]
[TD="align: right"]$1.25[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$1.25[/TD]
[TD]Client Criteria[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$1.00[/TD]
[TD]Multi-State Instant Criminal Check[/TD]
[TD="align: right"]$3.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$3.00[/TD]
[TD]Criminal Check by Jurisdiction - State: TN, County: Rutherford[/TD]
[TD="align: right"]$14.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$14.00[/TD]
[TD]Nationwide Sex Offender Registry Check[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD]Criminal Check by County - State: FL[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD]Criminal Check by County - State: NC[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]5/24/2017 16:57
[/TD]
[TD]Applicant Report[/TD]
[TD="align: right"]106550443[/TD]
[TD]Harold Julius Brown[/TD]
[TD]Package 1: Pre Hire Full BGC[/TD]
[TD] [/TD]
[TD]SGB - Shiftgig Bullpen, Inc.[/TD]
[TD]Phoenix
[/TD]
[TD]jruiz@shiftgig.com[/TD]
[TD="align: right"]$19.25[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$19.25[/TD]
[TD]SSN Trace[/TD]
[TD="align: right"]$1.25[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$1.25[/TD]
[TD]Client Criteria[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$1.00[/TD]
[TD]Multi-State Instant Criminal Check[/TD]
[TD="align: right"]$3.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$3.00[/TD]
[TD]Criminal Check by Jurisdiction - State: AZ, County: Maricopa[/TD]
[TD="align: right"]$14.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$14.00[/TD]
[TD]Nationwide Sex Offender Registry Check[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD]Criminal Check by County - State: VA, County: Henrico[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6/14/2017 1:40[/TD]
[TD]Applicant Report[/TD]
[TD="align: right"]107937037[/TD]
[TD]Trayvione Lamarque Leonard[/TD]
[TD="colspan: 2"]Package 1: Pre Hire Full BGC[/TD]
[TD]SGB - Shiftgig Bullpen, Inc.[/TD]
[TD]Houston[/TD]
[TD]paige.kilchrist@shiftgig.com[/TD]
[TD="align: right"]$19.25[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$19.25[/TD]
[TD]SSN Trace[/TD]
[TD="align: right"]$1.25[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$1.25[/TD]
[TD]Client Criteria[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$1.00[/TD]
[TD]Multi-State Instant Criminal Check[/TD]
[TD="align: right"]$3.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$3.00[/TD]
[TD]Criminal Check by Jurisdiction - State: TX, County: Harris[/TD]
[TD="align: right"]$14.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$14.00[/TD]
[TD]Nationwide Sex Offender Registry Check[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD]Criminal Check by County - State: VA[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Help With Arranging data in a spreadsheet using VBA

I get subscript out of range Error 9 on the below code would you know why?

Sub MG02Nov58()
Dim Ray As Variant, n As Long, c As Long, Ac As Long
Ray = Cells(1).CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * Int(UBound(Ray, 2) / 14), 1 To 17)
nray(1, 1) = "Date": nray(1, 2) = "Report Type": nray(1, 3) = "Applicant ID": nray(1, 4) = "Applicant Name"
nray(1, 5) = "Package Name": nray(1, 6) = "Reference": nray(1, 7) = "Billing Code": nray(1, 8) = "Location": nray(1, 9) = "Run By": nray(1, 10) = "Base": nray(1, 11) = "Fees"
nray(1, 12) = "Total": nray(1, 13) = "Description": nray(1, 14) = "Base": nray(1, 15) = "Fees": nray(1, 16) = "Total"
c = 1
For n = 2 To UBound(Ray, 1)
For Ac = 4 To UBound(Ray, 2) Step 4
If Ray(n, Ac) <> "" Then
c = c + 1
nray(c, 1) = Ray(n, 1)
nray(c, 2) = Ray(n, 2)
nray(c, 3) = Ray(n, 3)
nray(c, 4) = Ray(n, 4)
nray(c, 5) = Ray(n, 5)
nray(c, 6) = Ray(n, 6)
nray(c, 7) = Ray(n, 7)
nray(c, 8) = Ray(n, 8)
nray(c, 9) = Ray(n, 9)
nray(c, 10) = Ray(n, 10)
nray(c, 11) = Ray(n, 11)
nray(c, 12) = Ray(n, 12)
nray(c, 13) = Ray(n, Ac)
nray(c, 14) = Ray(n, Ac + 1)
nray(c, 15) = Ray(n, Ac + 2)
nray(c, 16) = Ray(n, Ac + 3)
End If
Next Ac
Next n
With Sheets("Sheet2").Range("A1").Resize(c, 16)
.Value = nray
.Borders.Weight = 2
.Columns.AutoFit
End With
End Sub
 
Upvote 0
Re: Help With Arranging data in a spreadsheet using VBA

Try this for results on sheet2.
The results list now has 16 columns, that's 12 basic columns then 4 extra columns for each set of 4 (Component, Base, Fee, Total) columns.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG03Nov32
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Cells(1).CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * Int(UBound(Ray, 2) / 4), 1 To 16)
nray(1, 13) = "Component": nray(1, 14) = "Base": nray(1, 15) = "Fees": nray(1, 16) = "Total"
c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]For[/COLOR] Ac = 13 To UBound(Ray, 2) [COLOR="Navy"]Step[/COLOR] 4
      [COLOR="Navy"]If[/COLOR] Ray(n, Ac) <> "" [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            [COLOR="Navy"]For[/COLOR] nn = 1 To 12
                nray(1, nn) = Ray(1, nn)
                nray(c, nn) = Ray(n, nn)
            [COLOR="Navy"]Next[/COLOR] nn
            
            nray(c, 13) = Ray(n, Ac)
            nray(c, 14) = Ray(n, Ac + 1)
            nray(c, 15) = Ray(n, Ac + 2)
            nray(c, 16) = Ray(n, Ac + 3)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 16)
    .Value = nray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Re: Help With Arranging data in a spreadsheet using VBA

Thanks a Tonn!!

Mick you saved me a tonn of Man hours
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
S
  • Question Question
Macro to extract Columns based on multiple criteria
Replies
6
Views
4K
Trebor76
T
D
  • Question Question
How to output a dynamic range where the height of the range varies
Replies
0
Views
1K
dagda13
D
C
  • Question Question
Sum
Replies
3
Views
1K
pbornemeier
pbornemeier
JTL9161
  • Question Question
Sorting Rank Results
Replies
4
Views
1K
JTL9161
JTL9161

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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