excel - VBA code not working in Excel with dataset

Question

I am working on a machine learning project and am using Excel to handle the dataset. I am new to both Excel and VBA.

So I am using this dataset, and I just copy pasted the whole thing into an excel spreadsheet. I did text to columns. Here's a snapshot of some of the data:

Snapshot of data

I want to reformat the data in the spreadsheet so that all of the data goes into a single row, then starts a new row after the "name" keyword.

For example, I want this:

1 2 3 4 5 6 7 8 9 10

11 12 13 14 15 16 17 18

19 20 21 22 23 name

to become:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 name (all on one line)

without having to do it manually line by line.

I used the below VBA code to format the data how I want it:

Sub separateByName()
Dim lRow As Long
Dim lCol As Long
Dim lCol2 As Long

k = 1

lRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lRow
lCol = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 1 To lCol

    lCol2 = Sheets("Sheet2").Cells(k, Columns.Count).End(xlToLeft).Column
    Sheets("Sheet2").Cells(k, lCol2 + 1).Value = Cells(i, j).Value
    If Cells(i, j).Value = "name" Then k = k + 1

Next j
Next i
End Sub

However, when I run I'm getting problems in that the result seems randomly patterned.

This:

1 0 63 1 -9 -9 -9
-9 1 145 1 233 -9 50 20
1 -9 1 2 2 3 81 0
0 0 0 0 1 10.5 6 13
150 60 190 90 145 85 0 0
2.3 3 -9 172 0 -9 -9 -9
-9 -9 -9 6 -9 -9 -9 2
16 81 0 1 1 1 -9 1
-9 1 -9 1 1 1 1 1
1 1 -9 -9 name
2 0 67 1 -9 -9 -9
-9 4 160 1 286 -9 40 40
0 -9 1 2 3 5 81 0
1 0 0 0 1 9.5 6 13
108 64 160 90 160 90 1 0
1.5 2 -9 185 3 -9 -9 -9
-9 -9 -9 3 -9 -9 -9 2
5 81 2 1 2 2 -9 2
-9 1 -9 1 1 1 1 1
1 1 -9 -9 name

Became this:

1 0 63 1 -9 -9 -9 1 0 63 1 -9 -9 -9 -9 1 145 1 233 -9 50 20 1 -9 1 2 2 3 81 0 0 0 0 0 1 10.5 6 13 150 60 190 90 145 85 0 0 2.3 3 -9 172 0 -9 -9 -9 -9 -9 -9 6 -9 -9 -9 2 16 81 0 1 1 1 -9 1 -9 1 -9 1 1 1 1 1 1 1 -9 -9 name

  -9 1 145 1 233 -9 50 20 2 0 67 1 -9 -9 -9 -9 4 160 1 286 -9 40 40 0 -9 1 2 3 5 81 0 1 0 0 0 1 9.5 6 13 108 64 160 90 160 90 1 0 1.5 2 -9 185 3 -9 -9 -9 -9 -9 -9 3 -9 -9 -9 2 5 81 2 1 2 2 -9 2 -9 1 -9 1 1 1 1 1 1 1 -9 -9 name

The "name" is correctly at the end, but the actual data is messed up.

Could anyone help me to fix this code for my dataset?

Thanks!

标签： excelvbaformatdataset

Answer 1

I also tested your code with data and i got it to work just fine, just make sure on sheet 1 you have the data and you have empty sheet 2, then use the macro while sheet 1 is open. then your data is in sheet 2.