macro to shift columns in sequence

I have some data that I imported from a text file which I would like to represent as 512 rows by 512 columns.

After importing the data, it all showed up just fine. The problem is, its giving me columns 1 through 7 with all 512 rows, under which there is a blank row, then column headers 8 through 14 and their respective 512 rows. The pattern repeats in groups of 7 columns, all the way up to 512, giving me a long skinny rectangle where I want a square.

If there is any way I could shift all these columns to the proper shape without cutting and pasting manually, as I suspect there is, it would save me a considerable amount of work, seeing as I have a dozen or so of these files I need to format.

Thank you very much in advance, and please let me know if clarification on my issue is needed.

exposuresample.xlsx

I hope I attached that correctly.

The whole file did not fit, but the one I (may or may not have) posted here includes columns 1 through 24 of the 512 in the undesirable format.

I'd like to shift all the columns so that they are adjacent to one another, with all the values in rows 1 through 512 lined up.

In other words, I'd like the output of our macro to look like this:

desiredexposuresample.xlsx

exposuresample.xlsm

Hi there,

this code did the trick:

Option Explicit
Sub CorrectArray()
Dim sht As Worksheet
Dim XCol, XRow As Integer

Set sht = Sheets("Sheet1")

For XRow = 515 To sht.UsedRange.Rows.Count
  If sht.Cells(XRow, 1) = 1 Then
    XCol = sht.Cells(XRow - 2, 2)
    sht.Cells(XRow - 2, 2).Resize(514, 7).Copy
    sht.Cells(1, XCol + 1).PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False
    XRow = XRow + 514
  End If
Next XRow

sht.Cells(515, 1).Resize(sht.UsedRange.Rows.Count - 514, 1).EntireRow.Delete
End Sub

Try it on full size sample and let me know if it is OK...

So I hope i helps

Regards

M