Insert Rows

[rizmomin]

Hello:

I have excel sheet with name of Clients in Column B starting from cell B2 and goes on until B300
I want to insert 2 rows in between each client and name those cell as A & B and format it to the right of the cell.

Example as below:

Cell B2 : John Doe
B3 : Alen Smith
B4 : Wes Barb

change above to :

B2 : John Doe
B3 : A
B4 : B
B5 : Alen Smith
B6 : A
B7 : B
B8 : Wes Barb
B9 : A
B10: B
and so on..
Please let me know if any questions.

Thanks

RM

[wallyeye]

If this is a one-time thing, you can just insert a column before B, number each name in sequence. Then create a similar numbered list with A's and a list with B's, then sort the entire list by sequence.

[rizmomin]

Hello:

I do not think that would work for me since i have 300 rows of clients and need to add rows inbetween client names.

Please help with VB Code to perform the task.

Thanks

RM

[wallyeye]

Again, if this is a one-time thing, the method described above will work. If you need to do this repeatedly, do you need to retain formatting or formulas on the existing rows? Are there any criteria for including/excluding rows (totals, subtotals)?

[rizmomin]

Hello:

I think you are misunderstanding me.
Please refer to attached excel sheet as example.
This is showing just few customers in sheet1...overall i have over 350 customers.
Sheet2 is what i need to achive for all customers.

Please let me know if any questions.
Thanks a lot for helping.
Riz

[Leith Ross]

Hello Riz,

I have added a button to "Sheet2" to run the macro below. This will copy the data, add the needed rows, and right align the two newly added rows for each entry.

Sub AddRowsAndFormat()

    Dim Cell As Range
    Dim Data As Variant
    Dim DstRng As Range
    Dim r As Long
    Dim RngEnd As Range
    Dim SrcRng As Range
    
        Set SrcRng = Worksheets("Sheet1").Range("A1")
        Set DstRng = Worksheets("Sheet2").Range("A1")
        
            Set RngEnd = SrcRng.Parent.Cells(Rows.Count, SrcRng.Column).End(xlUp)
            If RngEnd.Row < SrcRng.Row Then Exit Sub
            
            Set SrcRng = SrcRng.Parent.Range(SrcRng, RngEnd).Resize(ColumnSize:=2)
            
            ReDim Data(1 To SrcRng.Rows.Count * 3, 1 To 2)
            
            Application.ScreenUpdating = False
            
                For Each Cell In SrcRng.Columns(1).Cells
                    r = r + 1
                    Data(r, 1) = Cell
                    Data(r, 2) = Cell.Offset(0, 1)
                    Data(r + 1, 1) = ""
                    Data(r + 1, 2) = "Invoices Generated"
                    Data(r + 2, 1) = ""
                    Data(r + 2, 2) = "Invoices Paid"
                    r = r + 2
                Next Cell
                
            DstRng.Parent.Columns(1).EntireColumn.Clear
            DstRng.Parent.Columns(2).EntireColumn.Clear
            
            Set DstRng = DstRng.Resize(UBound(Data, 1), 2)
            DstRng.Value = Data
            
            For r = 1 To DstRng.Rows.Count Step 3
                DstRng.Item(r + 1, 2).Resize(2, 1).HorizontalAlignment = xlHAlignRight
            Next r
            
            Application.ScreenUpdating = True
            
End Sub

[Sa DQ]

Option Explicit
Sub gpeMacro()
 Dim Cls As Range
 Dim Rws As Long:                           Dim MyStr As String
 
 Columns("A:A").Select:                     Selection.Insert Shift:=xlToRight
 Rws = Cells(Rows.Count, "C").End(xlUp).Row
 Cells(Rws, "A").Value = "GPE.COM":         Cells(1, "A").Value = "GPE.COM"
 
 For Each Cls In Range("C2:C" & Rws)
    MyStr = "A" & Cls.Row:                  Cls.Offset(, -2).Value = MyStr
    With Cells(Rows.Count, "A").End(xlUp).Offset(1)
        .Value = MyStr & "A":               .Offset(, 2).Value = "A"
        .Offset(1).Value = MyStr & "B":     .Offset(1, 2).Value = "B"
        .Offset(, 2).Resize(2).HorizontalAlignment = xlRight
    End With
 Next Cls
 Columns("A:C").Select
 Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1
 Columns("A:A").Delete
End Sub 

[wallyeye]

Genius Sa, exactly what I was talking about, I should have thought to do that in VBA.

[rizmomin]

Hi Leith:

This will work great for me...

Thanks a lot

Riz

[Leith Ross]

Hello Riz,

You're welcome.

[rizmomin]

Hi wallyeye:

Thanks a lot

Riz