Columns to Rows..?

[GFP1963]

Given data prepared as in the attached spreadsheet

Given a sheeet with Column A of values (say names) with corresponding columns B, C, D, etc. containing values relevant to row A,
how would you created additional rows for the names in Column A, 1 each for each value in columns B, C, D, etc.

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { color: white; font-size: 10pt; font-weight: 700; font-family: Calibri; text-align: left; background: black none repeat scroll 0% 0%; }.xl64 { font-size: 10pt; font-weight: 700; font-family: Calibri; }.xl65 { font-size: 10pt; font-weight: 700; font-family: Calibri; text-align: left; }.xl66 { font-size: 10pt; font-family: Calibri; }.xl67 { color: black; font-size: 10pt; font-family: Calibri; }.xl68 { color: white; font-size: 10pt; font-weight: 700; font-family: Calibri; background: black none repeat scroll 0% 0%; }.xl69 { color: white; font-size: 10pt; font-family: Calibri; background: black none repeat scroll 0% 0%; }</style> [TABLE="width: 432"]
<tbody>[TR]
[TD="class: xl63, width: 432, colspan: 6"]GIVEN THIS[/TD]
[/TR]
[TR]
[TD="class: xl65"]NAME[/TD]
[TD="class: xl64"]E1[/TD]
[TD="class: xl64"]E2[/TD]
[TD="class: xl64"]e3[/TD]
[TD="class: xl64"]e4[/TD]
[TD="class: xl64"]e5[/TD]
[/TR]
[TR]
[TD="class: xl66"] Sullivan[/TD]
[TD="class: xl66"]sullivan1[/TD]
[TD="class: xl66"]sullivan2[/TD]
[TD="class: xl66"]sullivan3[/TD]
[TD="class: xl66"]sullivan4[/TD]
[TD="class: xl66"]sullivan5[/TD]
[/TR]
[TR]
[TD="class: xl66"]Rodríguez[/TD]
[TD="class: xl66"]rodriguesz1[/TD]
[TD="class: xl66"]rodriguez2[/TD]
[TD="class: xl67"]rodriguez3[/TD]
[TD="class: xl67"]rodriguez4[/TD]
[TD="class: xl67"]rodriguez5[/TD]
[/TR]
[TR]
[TD="class: xl66"]Burns[/TD]
[TD="class: xl66"]burns1[/TD]
[TD="class: xl66"]burns1[/TD]
[TD="class: xl67"]burns3[/TD]
[TD="class: xl67"]burns4[/TD]
[TD="class: xl66"]burns5[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl68, colspan: 2"]END UP WITH THIS[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl66"] Sullivan[/TD]
[TD="class: xl66"]sullivan1[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"] Sullivan[/TD]
[TD="class: xl66"]sullivan2[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"] Sullivan[/TD]
[TD="class: xl66"]sullivan3[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"] Sullivan[/TD]
[TD="class: xl67"]sullivan4[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"] Sullivan[/TD]
[TD="class: xl66"]sullivan5[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Rodríguez[/TD]
[TD="class: xl66"]rodriguesz1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Rodríguez[/TD]
[TD="class: xl66"]rodriguesz2[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Rodríguez[/TD]
[TD="class: xl66"]rodriguesz3[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Rodríguez[/TD]
[TD="class: xl66"]rodriguesz4[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Rodríguez[/TD]
[TD="class: xl66"]rodriguesz5[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Burns[/TD]
[TD="class: xl66"]burns1[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Burns[/TD]
[TD="class: xl66"]burns2[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Burns[/TD]
[TD="class: xl66"]burns3[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Burns[/TD]
[TD="class: xl66"]burns4[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Burns[/TD]
[TD="class: xl66"]burns5[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Virici]

Using VBA and assuming raw data in sheet1 and output in sheet2 (code not tested):

Private Sub Transform()

n = 1

For i = 2 to 4
   For j = 2 to 6
      If IsEmpty(Sheet1.Cells(i, j)) Then
         GoTo ContRow
      Else
         Sheet2.Cells(n, 1).Value = Sheet1.Cells(i, 1).Value
         Sheet2.Cells(n, 2).Value = Sheet1.Cells(i, j).Value
         n = n + 1
      End If
Next j
ContRow:
Next i

End Sub

 

[GFP1963]

If only I knew what that meant.
Way over my head.

 

[Virici]

You can do it two ways, manually or using VBA code as stated in my previous reply.

Manually you can, for each row, select the range with relevant values and copy that range. Then select an output destination and paste-special, transposing the data.
That can also be achieved using formula.
See this page for a guide.

You can also use the code-solution, see file (appearently I can't find an option to attach a file). The code will count the number of column headers and rows in the input sheet and perform your request if you press CTRL+SHIFT+T.
Do note that the code does not allow for empty headers and it will continue from one row to the next row if it encounters an empty cell.

 

[GFP1963]

I am familiar with the TRANSPOSE function/formula but that would not be efficient for the number of rows we expect to have.
The code solution in the sample file you provided is awesome though! Thank you!
We can work with this.

How could I modify it if there were more rows like 'name' that were to be repeated with column e1, e2, e3, etc?
For example:

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 14pt; font-weight: 700; font-family: Calibri; }.xl64 { font-size: 14pt; font-family: Calibri; }</style> [TABLE="width: 972"]
<tbody>[TR]
[TD="class: xl63, width: 144"]First Name[/TD]
[TD="class: xl63, width: 144"]Last Name[/TD]
[TD="class: xl63, width: 144"]Company Name[/TD]
[TD="class: xl63, width: 108"]E1[/TD]
[TD="class: xl63, width: 108"]E2[/TD]
[TD="class: xl63, width: 108"]E3[/TD]
[TD="class: xl63, width: 108"]E4[/TD]
[TD="class: xl63, width: 108"]E5[/TD]
[/TR]
[TR]
[TD="class: xl64"]Roy[/TD]
[TD="class: xl64"] Sullivan[/TD]
[TD="class: xl64"]ABC Company[/TD]
[TD="class: xl64"]sullivan1[/TD]
[TD="class: xl64"]sullivan2[/TD]
[TD="class: xl64"]sullivan3[/TD]
[TD="class: xl64"]sullivan4[/TD]
[TD="class: xl64"]sullivan5[/TD]
[/TR]
[TR]
[TD="class: xl64"]Maria[/TD]
[TD="class: xl64"]Rodr’guez[/TD]
[TD="class: xl64"]XYZ Company[/TD]
[TD="class: xl64"]rodriguesz1[/TD]
[TD="class: xl64"]rodriguez2[/TD]
[TD="class: xl64"]rodriguez3[/TD]
[TD="class: xl64"]rodriguez4[/TD]
[TD="class: xl64"]rodriguez5[/TD]
[/TR]
[TR]
[TD="class: xl64"]Joseph[/TD]
[TD="class: xl64"]Burns[/TD]
[TD="class: xl64"]123 Company[/TD]
[TD="class: xl64"]burns1[/TD]
[TD="class: xl64"]burns1[/TD]
[TD="class: xl64"]burns3[/TD]
[TD="class: xl64"]burns4[/TD]
[TD="class: xl64"]burns5[/TD]
[/TR]
</tbody>[/TABLE]

would end up with

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 14pt; font-weight: 700; font-family: Calibri; }.xl64 { font-size: 14pt; font-family: Calibri; }</style> [TABLE="width: 540"]
<tbody>[TR]
[TD="class: xl63, width: 144"]First Name[/TD]
[TD="class: xl63, width: 144"]Last Name[/TD]
[TD="class: xl63, width: 144"]Company Name[/TD]
[TD="class: xl63, width: 108"]E1[/TD]
[/TR]
[TR]
[TD="class: xl64"]Roy[/TD]
[TD="class: xl64"] Sullivan[/TD]
[TD="class: xl64"]ABC Company[/TD]
[TD="class: xl64"]sullivan1[/TD]
[/TR]
[TR]
[TD="class: xl64"]Roy[/TD]
[TD="class: xl64"] Sullivan[/TD]
[TD="class: xl64"]ABC Company[/TD]
[TD="class: xl64"]sullivan2[/TD]
[/TR]
[TR]
[TD="class: xl64"]Roy[/TD]
[TD="class: xl64"] Sullivan[/TD]
[TD="class: xl64"]ABC Company[/TD]
[TD="class: xl64"]sullivan3[/TD]
[/TR]
[TR]
[TD="class: xl64"]Roy[/TD]
[TD="class: xl64"] Sullivan[/TD]
[TD="class: xl64"]ABC Company[/TD]
[TD="class: xl64"]sullivan4[/TD]
[/TR]
[TR]
[TD="class: xl64"]Roy[/TD]
[TD="class: xl64"] Sullivan[/TD]
[TD="class: xl64"]ABC Company[/TD]
[TD="class: xl64"]sullivan5[/TD]
[/TR]
</tbody>[/TABLE]


I see where in the VBA editor the module is to edit it.

Thanks again!

 

[arunsjain]

Hi,

You use power query for this.

Step 1: Convert your data in table form (select your data and press CTRL + T).

Step 2: Go to Data tab in ribbon and click on "From Table". Power query window will open.

Step 3: Select Column E1 to E5 and go to "Transform tab" in ribbon. Click on "Unpivot columns".

Step 4: Close and load in "Home tab" in ribbon.

A new tab will be created and your table will be like below:

[TABLE="width: 344"]
<tbody>[TR]
[TD="width: 91"]First Name
[/TD]
[TD="width: 90"]Last Name
[/TD]
[TD="width: 125"]Company Name
[/TD]
[TD="width: 77"]Attribute
[/TD]
[TD="width: 74"]Value
[/TD]
[/TR]
[TR]
[TD]Roy
[/TD]
[TD]Sullivan
[/TD]
[TD]ABC Company
[/TD]
[TD]E1
[/TD]
[TD]sullivan1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Roy
[/TD]
[TD="bgcolor: transparent"]Sullivan
[/TD]
[TD="bgcolor: transparent"]ABC Company
[/TD]
[TD="bgcolor: transparent"]E2
[/TD]
[TD="bgcolor: transparent"]sullivan2
[/TD]
[/TR]
[TR]
[TD]Roy
[/TD]
[TD]Sullivan
[/TD]
[TD]ABC Company
[/TD]
[TD]E3
[/TD]
[TD]sullivan3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Roy
[/TD]
[TD="bgcolor: transparent"]Sullivan
[/TD]
[TD="bgcolor: transparent"]ABC Company
[/TD]
[TD="bgcolor: transparent"]E4
[/TD]
[TD="bgcolor: transparent"]sullivan4
[/TD]
[/TR]
[TR]
[TD]Roy
[/TD]
[TD]Sullivan
[/TD]
[TD]ABC Company
[/TD]
[TD]E5
[/TD]
[TD]sullivan5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Maria
[/TD]
[TD="bgcolor: transparent"]Rodr’guez
[/TD]
[TD="bgcolor: transparent"]XYZ Company
[/TD]
[TD="bgcolor: transparent"]E1
[/TD]
[TD="bgcolor: transparent"]rodriguesz1
[/TD]
[/TR]
[TR]
[TD]Maria
[/TD]
[TD]Rodr’guez
[/TD]
[TD]XYZ Company
[/TD]
[TD]E2
[/TD]
[TD]rodriguez2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Maria
[/TD]
[TD="bgcolor: transparent"]Rodr’guez
[/TD]
[TD="bgcolor: transparent"]XYZ Company
[/TD]
[TD="bgcolor: transparent"]E3
[/TD]
[TD="bgcolor: transparent"]rodriguez3
[/TD]
[/TR]
[TR]
[TD]Maria
[/TD]
[TD]Rodr’guez
[/TD]
[TD]XYZ Company
[/TD]
[TD]E4
[/TD]
[TD]rodriguez4
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Maria
[/TD]
[TD="bgcolor: transparent"]Rodr’guez
[/TD]
[TD="bgcolor: transparent"]XYZ Company
[/TD]
[TD="bgcolor: transparent"]E5
[/TD]
[TD="bgcolor: transparent"]rodriguez5
[/TD]
[/TR]
[TR]
[TD]Joseph
[/TD]
[TD]Burns
[/TD]
[TD]123 Company
[/TD]
[TD]E1
[/TD]
[TD]burns1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Joseph
[/TD]
[TD="bgcolor: transparent"]Burns
[/TD]
[TD="bgcolor: transparent"]123 Company
[/TD]
[TD="bgcolor: transparent"]E2
[/TD]
[TD="bgcolor: transparent"]burns1
[/TD]
[/TR]
[TR]
[TD]Joseph
[/TD]
[TD]Burns
[/TD]
[TD]123 Company
[/TD]
[TD]E3
[/TD]
[TD]burns3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Joseph
[/TD]
[TD="bgcolor: transparent"]Burns
[/TD]
[TD="bgcolor: transparent"]123 Company
[/TD]
[TD="bgcolor: transparent"]E4
[/TD]
[TD="bgcolor: transparent"]burns4
[/TD]
[/TR]
[TR]
[TD]Joseph
[/TD]
[TD]Burns
[/TD]
[TD]123 Company
[/TD]
[TD]E5
[/TD]
[TD]burns5
[/TD]
[/TR]
</tbody>[/TABLE]

You can also watch these steps on youtube. Search for "How to do unpivot in Power Query". Further in future if add data in your data table, just click on refresh all. Query table will be refreshed accordingly. In new tab you will find new data which you have added in your data table.

Hope it will help.

Cheers!!!!!!!

 

[GFP1963]

Thanks again for all your assistance. I am dead in the water with the Power Query - I work on a Mac which does not have that option. I will make use of what you have provided prior and/or get someone with a PC to jump in. Thank you again.

 

[GFP1963]

arunsjain - since I do not have, or have access to a version of excel with the Power Query would it be too much to ask if you could modify the version of the sheet with the VBA code solution to provide for 6 columns of information before the columns that would be transposed to rows. So:

[TABLE="width: 800"]
<tbody>[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Domain[/TD]
[TD]Company[/TD]
[TD]Extra 1[/TD]
[TD]Extra 2[/TD]
[TD]e1[/TD]
[TD]e2[/TD]
[TD]e3[/TD]
[TD]e4[/TD]
[TD]e5[/TD]
[TD]e6[/TD]
[/TR]
[TR]
[TD]Ray[/TD]
[TD]Suliivan[/TD]
[TD]abc.com[/TD]
[TD]ABC[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]sullivan1[/TD]
[TD]sullivan2[/TD]
[TD]sullivan3[/TD]
[TD]sullivan4[/TD]
[TD]sullivan5[/TD]
[TD]sullivan6[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Burns[/TD]
[TD]xyz.com[/TD]
[TD]XYZ[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]burns1[/TD]
[TD]burns2[/TD]
[TD]burns3[/TD]
[TD]burns4[/TD]
[TD]burns5[/TD]
[TD]burns6[/TD]
[/TR]
</tbody>[/TABLE]

would return

[TABLE="width: 600"]
<tbody>[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Domain[/TD]
[TD]Company[/TD]
[TD]Extra1[/TD]
[TD]Extra2[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Ray[/TD]
[TD]Sullivan[/TD]
[TD]abc.com[/TD]
[TD]ABC[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]sullivan1[/TD]
[/TR]
[TR]
[TD]Ray[/TD]
[TD]Sullivan[/TD]
[TD]abc.com[/TD]
[TD]ABC[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]sullivan2[/TD]
[/TR]
[TR]
[TD]Ray[/TD]
[TD]Sullivan[/TD]
[TD]abc.com[/TD]
[TD]ABC[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]sullivan3[/TD]
[/TR]
[TR]
[TD]Ray[/TD]
[TD]Sullivan[/TD]
[TD]abc.com[/TD]
[TD]ABC[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]sullivan4[/TD]
[/TR]
[TR]
[TD]Ray[/TD]
[TD]Sullivan[/TD]
[TD]abc.com[/TD]
[TD]ABC[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]sullivan5[/TD]
[/TR]
[TR]
[TD]Ray[/TD]
[TD]Sullivan[/TD]
[TD]abc.com[/TD]
[TD]ABC[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]sullivan6[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Burns[/TD]
[TD]xyz.com[/TD]
[TD]XYZ[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]burns1[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Burns[/TD]
[TD]xyz.com[/TD]
[TD]XYZ[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]burns2[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Burns[/TD]
[TD]xyz.com[/TD]
[TD]XYZ[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]burns3[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Burns[/TD]
[TD]xyz.com[/TD]
[TD]XYZ[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]burns4[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Burns[/TD]
[TD]xyz.com[/TD]
[TD]XYZ[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]burns5[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Burns[/TD]
[TD]xyz.com[/TD]
[TD]XYZ[/TD]
[TD]n/a[/TD]
[TD]n/a[/TD]
[TD]burns6[/TD]
[/TR]
</tbody>[/TABLE]

It would be greatly appreciated.

 

[GFP1963]

Actually I meant to ask Virici as he was the one that had produced the spreadsheet

 

[jolivanes]

Try this

Sub AAAAA()
Dim lr As Long, i As Long, lc As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Sheet2").Range("A1:D1").Value = Array("First Name", "Last Name", "Company Name", "E1")
    For i = 2 To lr
        lc = Cells(i, Columns.Count).End(xlToLeft).Column
            With Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Offset(1).Resize(lc - 3)
                .Value = Application.Transpose(Range(Cells(i, 4), Cells(i, lc)).Value)
                .Offset(, -1).Value = Cells(i, 3).Value
                .Offset(, -2).Value = Cells(i, 2).Value
                .Offset(, -3).Value = Cells(i, 1).Value
            End With
    Next i
Application.ScreenUpdating = True
End Sub