Insert row when value changes, sort the isolated information by date and then by description and sum

Simona · Sep 8, 2010

[FONT=&quot]Hello!
I need your help to build a macro that can do this manual and boring task for me.
I have a list of information and I need to:
1 – Insert two rows every time the code in the column A changes
2 – Sort every single new interval by date (column B) and then by description (column D)
3 – Sum the values in every single new interval (column C)
Do you think this is possible or is it asking too much????
Thank you in advance for your help!
Simona

I hope you can understand it from here:
It lost all the formatting though....<o

></o

>[/FONT]
[FONT=&quot]Before<o

></o

>[/FONT]
<table class="MsoNormalTable" style="width: 395.45pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="527"> <tbody><tr style="height: 15.75pt;"> <td style="width: 38.5pt; padding: 0cm; height: 15.75pt;" width="51">
</td> <td style="width: 69.75pt; padding: 0cm; height: 15.75pt;" width="93">

[FONT=&quot]A<o

></o

>[/FONT]

</td> <td style="width: 81.75pt; padding: 0cm; height: 15.75pt;" width="109">

[FONT=&quot]B<o

></o

>[/FONT]

</td> <td style="width: 67.2pt; padding: 0cm; height: 15.75pt;" width="90">

[FONT=&quot]C<o

></o

>[/FONT]

</td> <td style="width: 138.25pt; padding: 0cm; height: 15.75pt;" width="184">

[FONT=&quot]D<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Analysis Code<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Transaction Date<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Base Amount<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Description<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]2<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011101<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]31/08/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]2,047.50<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]BOULANG<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]3<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011101<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]20/07/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]27.53<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]ANT PATISSERIE<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]4<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011102<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]04/08/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]531.00<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]TANT<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]5<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011102<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]31/07/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]-1,350.48<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Soft Drink Accrual<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]6<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011102<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]31/08/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]-14,869.60<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Soft Drinks Stock<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]7<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011201<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]31/08/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]-20,086.75<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Food Stock<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]8<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011201<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]31/07/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]18,446.90<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Food Stock<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]9<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011201<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]31/07/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]-2,140.37<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Food Accrual<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]10<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011202<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]18/08/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]595.80<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]FRESH DISP<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]11<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011202<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]07/08/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]86.59<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]ABC FOOD<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;"> [FONT=&quot]After<o

></o

>[/FONT]
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15.75pt;"> <td style="padding: 0cm; height: 15.75pt;">
</td> <td style="padding: 0cm; height: 15.75pt;">

[FONT=&quot]A<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15.75pt;">

[FONT=&quot]B<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15.75pt;">

[FONT=&quot]C<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15.75pt;">

[FONT=&quot]D<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Analysis Code<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Transaction Date<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Base Amount<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Description<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]2<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011101<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]20/07/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]27.53<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]ANT PATISSERIE<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]3<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011101<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]31/08/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]2,047.50<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]BOULANG<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]4<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]sum<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]2,075.03<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]5<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]6<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011102<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]31/07/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]-1,350.48<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Soft Drink Accrual<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]7<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011102<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]31/08/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]-14,869.60<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Soft Drinks Stock<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]8<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011102<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]04/08/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]531.00<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]TANT<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]9<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]sum<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]-15,689.08<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]10<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]11<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011201<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]31/08/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]-20,086.75<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Food Stock<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]12<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011201<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]31/07/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]18,446.90<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Food Stock<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]13<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011201<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]31/07/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]-2,140.37<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]Food Accrual<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]14<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]sum<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]-3,780.22<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]15<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]16<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011202<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]18/08/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]595.80<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]FRESH DISP<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]17<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]1011202<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]07/08/2010<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]86.59<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]ABC FOOD<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15pt;"> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">
</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]sum<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">

[FONT=&quot]682.39<o

></o

>[/FONT]

</td> <td style="padding: 0cm; height: 15pt;">
</td> </tr> </tbody></table>

hiker95 · Sep 8, 2010

Simona,

What version of Excel are you using?

Simona · Sep 8, 2010

Hi hiker95,

I'm using Excel 2007!

hiker95 · Sep 8, 2010

Simona,

If it wasn't for the extra space between groups you could use Data, Subtotals.

Something like this.

Excel Workbook

A

B

C

D

1

Analysis Code

Transaction Date

Base Amount

Description

2

1011101

31/08/2010

2,047.50

BOULANG

3

1011101

20/07/2010

27.53

ANT PATISSERIE

4

1011101 Total

2075.03

5

1011102

4/8/2010

531

TANT

6

1011102

31/07/2010

-1,350.48

Soft Drink Accrual

7

1011102

31/08/2010

-14,869.60

Soft Drinks Stock

8

1011102 Total

-15,689.08

9

1011201

31/08/2010

-20,086.75

Food Stock

10

1011201

31/07/2010

18,446.90

Food Stock

11

1011201

31/07/2010

-2,140.37

Food Accrual

12

1011201 Total

-3,780.22

13

1011202

18/08/2010

595.8

FRESH DISP

14

1011202

7/8/2010

86.59

ABC FOOD

15

1011202 Total

682.39

16

Grand Total

-16711.88

17

Sheet1

hiker95 · Sep 8, 2010

Simona,

How should the data be sorted?

1. Column A, ascending (for sure).

2. Column B, ascending or descending?

3. Column D, ascending or descending?

MickG · Sep 8, 2010

Hi, Try this:-

Code:

[COLOR="Navy"]Sub[/COLOR] MG08Sep58
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
   [COLOR="Navy"]For[/COLOR] Rw = rng.Count + 1 To 2 [COLOR="Navy"]Step[/COLOR] -1
    [COLOR="Navy"]With[/COLOR] Range("A" & Rw)
        [COLOR="Navy"]If[/COLOR] Not .Value = .Offset(-1).Value [COLOR="Navy"]Then[/COLOR]
            .Resize(2).EntireRow.Insert
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] Rw
[COLOR="Navy"]Set[/COLOR] rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] rng = rng.SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]Dim[/COLOR] tot
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] rng.Areas
    R.Resize(, 4).Sort Range(R(1).Offset(, 1).Address), xlAscending
    R(R.Count + 1, 2) = "Sum"
    R(R.Count + 1, 3) = Application.Sum(R.Offset(, 2))
[COLOR="Navy"]Next[/COLOR] R
Range("A2").Resize(2).EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Simona · Sep 8, 2010

Hi hiker95,

thank you for your prompt response.
I've tried this solution, which works fine, but then I need to go in every single new interval and sort it by date and description.

I've just found this code to add the rows every time the code in the column A changes:

Sub AddRows()
Dim LR As Long
Dim Rw As Long

Application.ScreenUpdating = False

LR = Range("A" & Rows.Count).End(xlUp).Row

For Rw = LR - 1 To 2 Step -1
If Range("A" & Rw) <> Range("A" & Rw + 1) Then _
Range("A" & Rw + 1).EntireRow.Insert xlShiftDown
Next Rw
Application.ScreenUpdating = True

End Sub

but I don't know how to add 2 two rows (it's adding just one) and then sort the information by date and by description.
Do you have any idea?

hiker95 · Sep 8, 2010

MickG,

Very nice.

Your code example will save me a lot of time and effort in the future.

What a great place for learning.

Thanks again.

Simona · Sep 8, 2010

Hi hiker95 and MickG,

thank you for your help!!!
The code is working perfectly!

Just a couple of questions:

1) what should I change in the code if I want to sort first by description and then by date??

2) what should I change in the code if I realise that the description is not anymore in the column D but E??

3) how can i get the sum to be always in the same column C. At the moment the code is returning it in the column E!

Thank you

!

MickG · Sep 8, 2010

Simona said:
Hi hiker95 and MickG,

thank you for your help!!!
The code is working perfectly!

Just a couple of questions:

1) what should I change in the code if I want to sort first by description and then by date??

ans (1):-
I'm not sure how that works!! My code sorts all 4 columns Based on the Dates column. You could sort the same 4 columns based on the Description Column, but how would you sort on "Description" and then the "Dates" so you could see the results, unless they where sorted individually, then all the related data would be mixed up. !!!!

2) what should I change in the code if I realise that the description is not anymore in the column D but E??

Ans(2):-
If you want to alter the code to relect Column "E" (increase data columns to 5) as the description Column , Change the line as below to "5"

Code:

R.Resize(, 5).Sort Range(R(1).Offset(, 1).Address), xlAscending

3) how can i get the sum to be always in the same column C. At the moment the code is returning it in the column E!

Ans(3) :-_
I Don't understand how the results end up in column "E", Can you post some data , that this happens with, ??

Thank you !

Regards Mick

Insert row when value changes, sort the isolated information by date and then by description and sum

Simona

New Member

Excel Facts

hiker95

Well-known Member

Simona

New Member

hiker95

Well-known Member

hiker95

Well-known Member

MickG

MrExcel MVP

Simona

New Member

hiker95

Well-known Member

Simona

New Member

MickG

MrExcel MVP

Similar threads

Forum statistics

Share this page

Insert row when value changes, sort the isolated information by date and then by description and sum

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

MrExcel MVP

New Member

Well-known Member

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock