Working row by row

[Gazza2]

Need some help with moving data in excel.

I have a report that is imported into excel.

I then have the following code runningto bring the data into the right columns :

Do

If VBA.IsEmpty(Columns(LastCol).Value) Then
MsgBox "No data in this column"
Else
Set firstNonEmptyCell = Columns(LastCol).End(xlDown)
firstNonEmptyCell.Select
Selection.EntireRow.Offset(1).Insert
Selection.Cut
Cells(firstNonEmptyCell.Offset(1).Row, 9).Select
ActiveSheet.Paste
Set firstNonEmptyCell = Columns(LastCol).End(xlDown)
firstNonEmptyCell.Cut
Cells(firstNonEmptyCell.Offset(1).Row, 8).Select
ActiveSheet.Paste
Range(Cells(firstNonEmptyCell.Offset(-1).Row, 1), Cells(firstNonEmptyCell.Offset(-1).Row, 7)).Select
Selection.Copy
Cells(firstNonEmptyCell.Row, 1).Select
ActiveSheet.Paste
Cells(firstNonEmptyCell.Row, 4).Select
Selection.Value = ""
End If

Loop Until LastCol = 9

The code works fine if there is only one row with extra data in it but if say row 1 has 13 columns and row 2 has 15 columns it works on row 2 first as the code takes the LastCol value from the worksheet.

What i need is for it to work one row at a time starting from row 1, so it will move the data from the extra columns in row 1 until it gets to column 9 then move onto row 2 until that gets to column 9 and so forth until it gets to the LastRow( which is set up when the form opens)

Hope this makes sense and thanks for the help in advance

Gareth

 

[techsupport1]

Hey Gareth,

What if you set it up to determine how many columns were in each row when it started and then execute from there? I'm not entirely sure what you are asking, if you could post a sample file that would make things a lot easier.