snoom82
07-21-2016, 06:53 AM
Hi,
I am trying to find a solution in order to consolidate based on column C, and delete the duplicated rows based on the same column.
What I started to do is maybe a complicated way. Basically I tried to select the current region and offset it in order to select from column C, set this selection as a named range, select the 5th empty cell row C and consolidate the named range (which does not work and send me the error 1004)
Sub consolidate()
Dim test As Range
Range("A3").Select
Selection.CurrentRegion.Select
Selection.Offset(2, 2).Select
Set test = Selection
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row + 5
Range("C" & LastRow).Select
Dim DataRange As Range
Selection.consolidate Sources:= _
Range("test"), Function:= _
xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
End Sub '
The problem is I didn;t manage to make the consolidation work, and I am far from being able to start removing the duplicate rows...
is anyone has a better idea? I am sure there is a better and easier solution, my VBA level doesn;t allow me to find it...
the excel layout is as below
Column A
16696
21/07/2016
Week
29
Material
Description
Customer
Teteghem Stock
In Transit
UK Stock
Call Off
Production
***A
material A
20021593
9,337
8,947
***E
material E
20028448
232,622
***E
material E
20028448
7,391
161,046
292,139
***E
material E
20028448
14,782
17,894
393,668
***D
material D
20021438
8,943
***E
material E
20023371
249,738
16,727
***A
material A
20023363
751,548
105,029
***B
material B
20020682
2,374,456
32,290
***A
material A
20022901
572,608
***B
material B
20027870
7,390
***E
material E
20028448
232,233
***E
material E
20028448
53,682
***E
material E
20028448
4,279
***E
material E
20028448
166,492
I am trying to find a solution in order to consolidate based on column C, and delete the duplicated rows based on the same column.
What I started to do is maybe a complicated way. Basically I tried to select the current region and offset it in order to select from column C, set this selection as a named range, select the 5th empty cell row C and consolidate the named range (which does not work and send me the error 1004)
Sub consolidate()
Dim test As Range
Range("A3").Select
Selection.CurrentRegion.Select
Selection.Offset(2, 2).Select
Set test = Selection
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row + 5
Range("C" & LastRow).Select
Dim DataRange As Range
Selection.consolidate Sources:= _
Range("test"), Function:= _
xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
End Sub '
The problem is I didn;t manage to make the consolidation work, and I am far from being able to start removing the duplicate rows...
is anyone has a better idea? I am sure there is a better and easier solution, my VBA level doesn;t allow me to find it...
the excel layout is as below
Column A
16696
21/07/2016
Week
29
Material
Description
Customer
Teteghem Stock
In Transit
UK Stock
Call Off
Production
***A
material A
20021593
9,337
8,947
***E
material E
20028448
232,622
***E
material E
20028448
7,391
161,046
292,139
***E
material E
20028448
14,782
17,894
393,668
***D
material D
20021438
8,943
***E
material E
20023371
249,738
16,727
***A
material A
20023363
751,548
105,029
***B
material B
20020682
2,374,456
32,290
***A
material A
20022901
572,608
***B
material B
20027870
7,390
***E
material E
20028448
232,233
***E
material E
20028448
53,682
***E
material E
20028448
4,279
***E
material E
20028448
166,492