HELP auto insert 2 rows in a spreadsheet

[dollfin003]

Is it possible to have 2 rows automatically inserted after a name change in a spreadsheet. For example

Abhijeet Pradhan
Aby Abraham Moozikkal-91997
Aditha Suresh Bhatia
AGA Proj/Shameel Pannakar - 15227

so that it would look like this

Abhijeet Pradhan


Aby Abraham Moozikkal-91997


Aditha Suresh Bhatia


AGA Proj/Shameel Pannakar - 15227

 

[erik.van.geit]

Hi, dollfin003
Welcome to the Board !!!!!

you're talking about "after a name change"
but your example is showing another name on each row

do you need to insert 2 rows between each row
OR
would this be a better example ?
Abhijeet Pradhan
Abhijeet Pradhan
Abhijeet Pradhan


Aby Abraham Moozikkal-91997
Aby Abraham Moozikkal-91997


Aditha Suresh Bhatia

kind regards,
Erik

 

[dollfin003]

Exactly what you have shown. I have tried subtotals but it adds a row lowe than I need


Aby Abraham Moozikkal-91997 07/28/06 12:01PM EDT 2 27
865
Aby Abraham Moozikkal-91997 Total 865

Any help would be great

 

[erik.van.geit]

UPDATE: see page two

Hello,

try this one

Option Explicit

Sub insert_rows_on_each_change()
'Erik Van Geit
'080628

'EXAMPLE
'CC = 3, FR = 2, NR = 2
'START WITH
'a1  b1  header  d1
'a2  b2  A   d2
'a3  b3  A   d3
'a4  b4  B   d4
'a5  b5  C   d5
'a6  b6  C   d6
'RESULT
'a1  b1  header  d1
'a2  b2  A   d2
'a3  b3  A   d3
'
'
'a4  b4  B   d4
'
'
'a5  b5  C   d5
'a6  b6  C   d6

Dim rng As Range
Dim LR As Long              'Last Row
Dim CC As Long
Dim FR As Long
Dim NR As Long

'***** EDIT the following lines ****
CC = 1        'Check this Column
FR = 1        'First Row with data: MINIMUM = 2
NR = 2        'Number of Rows to insert
'***** END EDIT ****

Application.ScreenUpdating = False

LR = Cells(Rows.Count, CC).End(xlUp).Row
Columns(CC).EntireColumn.Insert

Set rng = Range(Cells(FR + 1, CC), Cells(LR, CC))

Cells(FR, CC) = 1

    With rng
    .FormulaR1C1 = "=IF(RC[1]=R[-1]C[1],R[-1]C,R[-1]C+1)"
    .Value = .Value
        With .Offset(.Rows.Count, 0)
        .Cells(1, 1).Value = 1
            With .Resize(.Cells(1, 1).Offset(-1, 0) - 1, 1)
            .DataSeries Rowcol:=xlColumns, Type:=xlLinear, step:=1
            .Copy .Resize(NR * .Rows.Count, 1)
            End With
        End With
    LR = Cells(Rows.Count, CC).End(xlUp).Row
    Range(Cells(FR, CC), Cells(LR, CC)).EntireRow.Sort Key1:=.Cells(1, 1)
    End With

Columns(CC).EntireColumn.Delete

Application.ScreenUpdating = True

End Sub

step trough the code using function key F8
you'll see the tricks behind the scene

best regards,
Erik

 

[dollfin003]

Hi Erik, Thank you for all your help. I am not EXCEL literate enough to even begn to understand how to do what you suggested LOL

Regards dolfin003

 

[erik.van.geit]

do you mean you need some clarification on using code ?

start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the code.

to run the code
click anywhere in the code and hit function key F5
or
via Excel menu: Tools / Macro / Macros (or hit Alt+F8)

assign shortcutkey
menu Tools/Macro/Macros
select your macro
click options
choose a character as shortcut: example T
to run the macro press Ctrl+Shift+T

 

[dollfin003]

Perfect you have given me hours of my life back. This works flawlessly

Thank you for all you help

Have an EXCELLENT day

Dollfin003

 

[dollfin003]

Hi Erik, One more thing. Now that I get the 2 lines seperating the data is there any way that it can do an autosum of that data for example

Abhijeet Pradhan 07/31/06 8:31AM EDT 3 66 $2.25
Abhijeet Pradhan 07/31/06 9:02AM EDT 1 1 $0.03
Abhijeet Pradhan 07/31/06 9:02AM EDT 3 107 $3.63
Abhijeet Pradhan 07/31/06 10:05AM EDT 3 75 $2.55
Aby Abraham Moozikkal-91997 07/05/06 9:35AM EDT 1 1 $0.03
Aby Abraham Moozikkal-91997 07/05/06 9:44AM EDT 1 1 $0.03
Aby Abraham Moozikkal-91997 07/05/06 9:50AM EDT 1 1 $0.03
Aditha Suresh Bhatia 07/06/06 11:31AM EDT 1 9 $0.31
Aditha Suresh Bhatia 07/26/06 8:51AM EDT 4 120 $4.07
AGA Proj/Shameel Pannakar - 15227 07/03/06 12:25AM EDT 1 1 $0.03
AGA Proj/Shameel Pannakar - 15227 07/03/06 12:25AM EDT 2 4 $0.13
AGA Proj/Shameel Pannakar - 15227 07/03/06 12:29AM EDT 2 3 $0.10


This would make my year if this is possible

Dollfin003

 

[erik.van.geit]

(almost) everything is possible with Excel
(f.i.: I'm using Excel to view photo's in a map)

take a look at SUBTOTAL
if you didn't want two rows inserted between the items, I would have suggested SUBTOTAL already

YOUR TABLE
   A                           B                C      D      E      F      
 1 HEADER                      HEADER           HEADER HEADER HEADER HEADER 
 2 Abhijeet Pradhan            07/31/06 8:31AM  EDT    3      66     2,25   
 3 Abhijeet Pradhan            07/31/06 9:02AM  EDT    1      1      0,03   
 4 Abhijeet Pradhan            07/31/06 9:02AM  EDT    3      107    3,63   
 5 Abhijeet Pradhan            07/31/06 10:05AM EDT    3      75     2,55   
 6 Aby Abraham Moozikkal-91997 07/05/06 9:35AM  EDT    1      1      0,03   
 7 Aby Abraham Moozikkal-91997 07/05/06 9:44AM  EDT    1      1      0,03   
 8 Aby Abraham Moozikkal-91997 07/05/06 9:50AM  EDT    1      1      0,03   
 9 Aditha Suresh Bhatia        07/06/06 11:31AM EDT    1      9      0,31   
10 Aditha Suresh Bhatia        07/26/06 8:51AM  EDT    4      120    4,07   

TABLE
[Table-It] version 05 by Erik Van Geit

WITH SUBTOTALS
   A                                 B                C      D      E      F      
 1 HEADER                            HEADER           HEADER HEADER HEADER HEADER 
 2 Abhijeet Pradhan                  07/31/06 8:31AM  EDT    3      66     2,25   
 3 Abhijeet Pradhan                  07/31/06 9:02AM  EDT    1      1      0,03   
 4 Abhijeet Pradhan                  07/31/06 9:02AM  EDT    3      107    3,63   
 5 Abhijeet Pradhan                  07/31/06 10:05AM EDT    3      75     2,55   
 6 Total Abhijeet Pradhan                                                  8,46   
 7 Aby Abraham Moozikkal-91997       07/05/06 9:35AM  EDT    1      1      0,03   
 8 Aby Abraham Moozikkal-91997       07/05/06 9:44AM  EDT    1      1      0,03   
 9 Aby Abraham Moozikkal-91997       07/05/06 9:50AM  EDT    1      1      0,03   
10 Total Aby Abraham Moozikkal-91997                                       0,09   
11 Aditha Suresh Bhatia              07/06/06 11:31AM EDT    1      9      0,31   
12 Aditha Suresh Bhatia              07/26/06 8:51AM  EDT    4      120    4,07   
13 Total Aditha Suresh Bhatia                                              4,38   
14 Endtotal                                                                12,93  

TABLE
[Table-It] version 05 by Erik Van Geit

RANGE   FORMULA (1st cell)
F6      =SUBTOTAL(9,F2:F5)
F10     =SUBTOTAL(9,F7:F9)
F13     =SUBTOTAL(9,F11:F12)
F14     =SUBTOTAL(9,F2:F12)

[Table-It] version 05 by Erik Van Geit

best regards,
Erik

 

[dollfin003]

So I understand correctly, I run this program via Macro after I subtotal or befor.

So the end esult that I am looking for is:

Abhijeet Pradhan 07/31/06 9:02AM EDT 3 107 $3.63
Abhijeet Pradhan 07/31/06 10:05AM EDT 3 75 $2.55
2,443 $83.00

Aby Abraham Moozikkal-91997 07/05/06 9:35AM EDT 1 1 $0.03
Aby Abraham Moozikkal-91997 07/05/06 9:44AM EDT 1 1 $0.03
Aby Abraham Moozikkal-91997 07/28/06 10:59AM EDT 2 17 $0.58
Aby Abraham Moozikkal-91997 07/28/06 12:01PM EDT 2 27 $0.92
865 $29.35

Aditha Suresh Bhatia 07/06/06 11:31AM EDT 1 9 $0.31
Aditha Suresh Bhatia 07/26/06 8:51AM EDT 4 120 $4.07
129 $4.38

AGA Proj/Shameel Pannakar - 15227 07/03/06 12:25AM EDT 1 1 $0.03


Correct ???