VBA for inserting rows

[Access Beginner]

Hi All,

I have a data set which in Column C has a list of postcodes, there are around 2,600 unique postcodes in this data set.

I was hoping that a macro could be written that would insert 3 rows after each postcode and then in Column D, put the following values CDAP, CRS and Total.

Data as it is now:

Column C
800
810
812
2486 etc

Desired Result:

Column C....... Column D
800 ...............CDAP
.....................CRS
.....................Total
810 ................CDAP
......................CRS
......................Total

And so on until the last postcode is down. Last row in the data set is 2603.

Please ignore the ............, this is the only why I could get it to align

Sorry but I am at work and cannot use any of the tools to paste the actual worksheet.

Any help is greatly appreciated

Using Excel 2007

 

[SydneyGeek]

Not sure if I got the placement correct but make a copy of the worksheet, put the CRS etc values into cells A1:A3 of Sheet3 (an otherwise blank sheet) and try this out:

Sub InsertRows()
    Dim rwLast As Long, _
        Rw As Long
    
    rwLast = Range("C" & Rows.Count).End(xlUp).Row
    
    For Rw = rwLast To 2 Step -1
        Cells(Rw, 3).Resize(3, 1).EntireRow.Insert
        Cells(Rw, 4).Resize(3, 1) = Sheets("Sheet3").Range("A1:A3").Value
    Next Rw
End Sub

Denis

 

[Rick Rothstein]

This code should do what you want...

Sub InsertRowsAndData()
  Dim X As Long, LastRow As Long
  Const StartRow As Long = 2
  Const RowsToInsert As Long = 2
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  For X = LastRow To StartRow Step -1
    Rows(X + 1).Resize(RowsToInsert).Insert
    Cells(X, "D").Resize(RowsToInsert + 1).Value = WorksheetFunction.Transpose(Array("CDAP", "CRS", "Total"))
  Next
End Sub

NOTE 1: Your description said you wanted to insert 3 rows, but actually, you are only inserting 2 (new) rows.

Note 2: There are two constants (the Const) statements that you should check the values being assigned. The first is the starting row for your data... I assumed Row 2. The second is the number of new rows to be inserted which, as Note 1 indicates, is set to 2.

 

[Access Beginner]

Thanks guys, your help is greatly appreciated.

Cheers
Haydn

 

[SydneyGeek]

Here's another option that places the first item on the same row as the value in column C:

Sub InsertRows()
    Dim rwLast As Long, _
        Rw As Long
    
    rwLast = Range("C" & Rows.Count).End(xlUp).Row
    
    For Rw = rwLast To 2 Step -1
        Cells(Rw, 3).Resize(2, 1).EntireRow.Insert
        Cells(Rw + 2, 4).Resize(3, 1) = Sheets("Sheet3").Range("A1:A3").Value
    Next Rw
    Range("C2:C3").EntireRow.Delete
End Sub

Denis