Insert Rows

[conoromuiri]

Hi,

I am importing data into one spreadsheet and I want to insert rows between each individual worksheets data.

For example the data will import stacked like this:

sheet1.xls
sheet1.xls
sheet1.xls
sheet2.xls
sheet2.xls
sheet2.xls

And I want to insert rows between sheet1.xls and sheet2.xls.

How can I do this?

[Trebor76]

Hi there,

Try this - just change the two variables to suit your needs:


Option Explicit
Sub Macro1()

Dim lngLastRow As Long, _
lngActiveRow As Long, _
lngToRow As Long
Dim strCol As String

lngToRow = 2 'First row containing the data. Change to suit.
strCol = "A" 'Column letter containing the data. Change to suit.

lngLastRow = Cells(Cells.Rows.Count, strCol).End(xlUp).Row

Application.ScreenUpdating = False

For lngActiveRow = lngLastRow To lngToRow Step -1
If lngActiveRow > lngToRow Then
If Cells(lngActiveRow, strCol) <> _
Cells(lngActiveRow - 1, strCol) Then
Rows(lngActiveRow).EntireRow.Insert
End If
End If
Next lngActiveRow

Application.ScreenUpdating = True

End Sub

HTH

Robert