Excel: Calculate two rows together and delete one

[benjamin.grimm]

Dear all,

i have a spreadsheet with data, which get imported to Excel. The spreadsheet is called "Rohdaten".

The data get transfered via VBA to the spreadsheet "TG2".

In the spreadsheet specific data get merged and sorted.

This works perfectly. See attached file.

The merged cells are one tender for a specific customer.

In the column 11 are the products and in the column 14 are the delivery Dates and in the column 15 are the amount of the prodcuts.


Example:

Column 5 / Column 6 / Column 7/ Column 11 / Column 14/ Column 15
Italy/ Rom/200/Produkt A / 2015 /20
Italy/ Rom /200/Produkt A / 2016/ 40
Italy/ Rom/200/Produkt A / 2016 /60
Italy/ Rom /200/Produkt B / 2016 /12
Italy/ Rom/200/ Produkt B/ 2016/ 12

So it should lokk like this.

Italy/ Rom/200/Produkt A / 2015 /20
Italy/ Rom /200/Produkt A / 2016/ 100
Italy/ Rom /200/Produkt B / 2016 /24


It would be perfect if vba could add all the products with the same delivery date and then delete the row.

Is this possbile?

Best Regards
Benjamin

Rows("9:200").Delete
With Sheets("Hilfstabelle").UsedRange
    .AutoFilter Field:=4, Criteria1:="E2"
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
End With
Sheets("TG2").Cells(Rows.Count, 2).End(xlUp).Offset(8, 0).PasteSpecial xlPasteValues
Sheets("Hilfstabelle").UsedRange.AutoFilter
 
Range("D8:AT200").Sort _
       Key1:=Range("h8"), order1:=xlDescending, Key2:=Range("n8"), order2:=xlAscending, _
       Header:=xlYes, MatchCase:=False, _
       Orientation:=xlTopToBottom
      
     Dim spalte As Integer
    Dim i As Long
     Application.DisplayAlerts = False
     With ActiveSheet
         For i = .Cells(.Rows.Count, 6).End(xlUp).Row To 10 Step -1
             If .Cells(i, 6) = .Cells(i - 1, 6) And Not IsEmpty(.Cells(i, 6)) Then
             
             For spalte = 4 To 8
               With .Range(.Cells(i - 1, spalte), .Cells(i, spalte))
            
                 .MergeCells = True
                 .VerticalAlignment = xlCenter
                 
                 
              If .Cells(i, 11) = .Cells(i - 1, 11) And Not IsEmpty(.Cells(i, 11)) Then
              .Cells(i - 1, 11) = .Cells(i, 11) + .Cells(i - 1, 11)
                       
            End If
             
            End With
            
            
             Next spalte
             
             End If
             
         Next i
         
     End With
     Application.DisplayAlerts = True
  
 
Dim lzeile As Long
 lzeile = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
 'Alle Daten des Blattes anzeigen
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
 'Rahmen ziehen
With Range("D9:AT" & lzeile).Borders
 .LineStyle = xlContinuous
 .ColorIndex = xlAutomatic
 .TintAndShade = 0
 .Weight = xlHairline
End With
 With Range("D9:AT" & lzeile).Borders
 .LineStyle = xlContinuous
 .ColorIndex = xlAutomatic
 .TintAndShade = 0
 .Weight = xlThin
End With
 
 Range("AR:AR").Select
Selection.NumberFormat = "0"
 Range("AT:AT").Select
Selection.NumberFormat = "mm.yy"

 

[llkhoutx]

Use the "SumIF" function is Excel. This question is posted in the wrong form.

 

[Brianwarnock]

You don't say where this is coming from, if Access then the simplest approach would be to do the aggregation in the Access query.

Brian