Add headers for each range within a table from the first row to the last row of the same repeating element

[leap out]

Hi

I want to add headers for each separate range and for each element, starting from the first row and ending with the last row for the same element, provided that the entire range is duplicated for the same element based on column A, and the headers must be the same as in the first row for all ranges, as in the second range, the headers are different For the headers of the first row of the first range, then the headers of the first row of the first range must be replaced with the headers of the second .as to third range should insert new row before the first row with a new element and after the last row of the element that precedes it, and the headings are the same as the headers of the first row for the first range
will be add new ranges , so the macro must be applied several times each time new ranges are added without affecting what was previously executed on some of the ranges that already existed.


before

items v .xlsm
	A	B	C	D	E	F	G	H
1	CLASSIFICATION	GOODS	MARK	MANFACTURE	BUYING	SELLING	NET	NOTE
2	OIL-AS2	MLO-1 10W40 208L TT/L	CAS	SU	23
3	OIL-AS2	10W40 208L	ENI	IT	12	11
4	OIL-AS2	5W30 208L	Q8	EU
5	OIL-AS2	10W40 208L	Q8	EU	11
6	ITEM	BRAND	SQ	PRTD4	ENTER	OUT	TOTAL	DEL.SS
7	OIL-AS3	10W40 12x1L	Q8	EU	111
8	OIL-AS3	10W40 12x1L	CAS	SU	111
9	OIL-AS3	10W40 12x1L	ENI	IT	112
10	OIL-AS3	OL-115W40 12x1L	CAS	SU**2	22
11	MS-OIL1	5W30 12x1L	Q8	EU	11
12	MS-OIL1	10W40 4x4L	Q8	EU	111		11
13	MS-OIL1	OIL/M 10W40 4x4L	CAS TRM	SU N3-90	33
14	MS-OIL1	10W40 4x4L MNH/80-T	ENI	IT L66RT	44
15	MS-OIL1	5W40 4x4L	Q8	EU	555
16	MS-OIL1	5W40 4x4L	CAS	SU	555
17	MS-OIL1	5W40 4x4L	ENI	IT	555
18	MS-OIL1	20W50 4x4L	Q8	EU	222
19	MS-OIL1	20W50 4x4L	CAS	SU	222
20	MS-OIL1	20W50 4x4L	ENI	IT	222
21	MS-OIL1	5W30 4x4L	Q8	EU	555
5

after

items v .xlsm
	A	B	C	D	E	F	G	H
1	CLASSIFICATION	GOODS	MARK	MANFACTURE	BUYING	SELLING	NET	NOTE
2	OIL-AS2	MLO-1 10W40 208L TT/L	CAS	SU	23
3	OIL-AS2	10W40 208L	ENI	IT	12	11
4	OIL-AS2	5W30 208L	Q8	EU
5	OIL-AS2	10W40 208L	Q8	EU	11
6	CLASSIFICATION	GOODS	MARK	MANFACTURE	BUYING	SELLING	NET	NOTE
7	OIL-AS3	10W40 12x1L	Q8	EU	111
8	OIL-AS3	10W40 12x1L	CAS	SU	111
9	OIL-AS3	10W40 12x1L	ENI	IT	112
10	OIL-AS3	OL-115W40 12x1L	CAS	SU**2	22
11	CLASSIFICATION	GOODS	MARK	MANFACTURE	BUYING	SELLING	NET	NOTE
12	MS-OIL1	5W30 12x1L	Q8	EU	11
13	MS-OIL1	10W40 4x4L	Q8	EU	111		11
14	MS-OIL1	OIL/M 10W40 4x4L	CAS TRM	SU N3-90	33
15	MS-OIL1	10W40 4x4L MNH/80-T	ENI	IT L66RT	44
16	MS-OIL1	5W40 4x4L	Q8	EU	555
17	MS-OIL1	5W40 4x4L	CAS	SU	555
18	MS-OIL1	5W40 4x4L	ENI	IT	555
19	MS-OIL1	20W50 4x4L	Q8	EU	222
20	MS-OIL1	20W50 4x4L	CAS	SU	222
21	MS-OIL1	20W50 4x4L	ENI	IT	222
22	MS-OIL1	5W30 4x4L	Q8	EU	555
5

 

[leap out]

you can check the file
Sample (1).xlsm

 

[Legacy 143009]

OK,

Fix the same line like this:

If Cells(i, 1).Value <> Cells(i - 1, 1).Value And Cells(i, 1).Value <> "CLASSIFICATION" And Cells(i - 1, 1).Value <> "CLASSIFICATION" Then

 

[leap out]

excellent!

just question . based on your code seems to identify ITEM word but if I have another range contains headers doesn't contain ITEM word then will not work correctly . so the question is there way doesn't depend on specific word especially if I have many headers don't contain ITEM word in (different words in column A for the header for each range)?

 

[Legacy 143009]

How can we differentiate regular rows from headers? Is there any identifier?

 

[leap out]

like this

Sample (1).xlsm
	A	B	C	D	E	F	G	H
1	CLASSIFICATION	GOODS	MARK	MANFACTURE	BUYING	SELLING	NET	NOTE
2	OIL-AS2	MLO-1 10W40 208L TT/L	CAS	SU	23
3	OIL-AS2	10W40 208L	ENI	IT	12	11
4	OIL-AS2	5W30 208L	Q8	EU
5	OIL-AS2	10W40 208L	Q8	EU	11
6	ITEM	BRAND	SQ	PRTD4	ENTER	OUT	TOTAL	DEL.SS
7	OIL-AS3	10W40 12x1L	Q8	EU	111
8	OIL-AS3	10W40 12x1L	CAS	SU	111
9	OIL-AS3	10W40 12x1L	ENI	IT	112
10	OIL-AS3	OL-115W40 12x1L	CAS	SU**2	22
11	MS-OIL1	5W30 12x1L	Q8	EU	11
12	MS-OIL1	10W40 4x4L	Q8	EU	111		11
13	MS-OIL1	OIL/M 10W40 4x4L	CAS TRM	SU N3-90	33
14	S.N	CODE	BATCH	IT CODE	INPUT	OUT	NET	REF
15	MS-OIL2	10W40 4x4L MNH/80-T	ENI	IT L66RT	44
16	MS-OIL2	5W40 4x4L	Q8	EU	555
17	I.S	ID	OR-FG	MTR-BR	BT Q	IMP	EX	DES
18	MS-OIL4	5W40 4x4L	CAS	SU	555
Sayfa3

How can we differentiate regular rows from headers?

just I ask you what do you think., what the better do that.

Is there any identifier?

do you mean we need helper column contains the headers in column A to match and do that?

 

[Legacy 143009]

do you mean we need helper column contains the headers in column A to match and do that?

I mean like for example, can we say, headers will always have gray background and regulars won't?

 

[leap out]

I mean like for example, can we say, headers will always have gray background

yes I will depend on gray background for all of the headers.

 

[Legacy 143009]

This should work if this is the same shade of gray in XL2BB. You must play with RGB value if it doesn't work.

Sub test()
  Dim lRow As Long, i As Long
  Dim rList As Range
 
  lRow = Cells(Rows.Count, 1).End(xlUp).Row
  With ActiveSheet
  Set rList = .ListObjects("Table1").Range 'your table name
  .ListObjects(1).Unlist 'convert to range
  .AutoFilterMode = False
  With Range("A1:A" & lRow)
  .AutoFilter 1, RGB(166, 166, 166), xlFilterCellColor
  On Error Resume Next
  .Offset(1).EntireRow.Delete
  End With
  .AutoFilterMode = False
  On Error GoTo 0
  lRow = Cells(Rows.Count, 1).End(xlUp).Row
  Application.ScreenUpdating = False
  For i = lRow To 3 Step -1
    If Cells(i, 1).Value <> Cells(i - 1, 1).Value And Cells(i, 1).Value <> "CLASSIFICATION" And Cells(i - 1, 1).Value <> "CLASSIFICATION" Then
      Rows(1).Copy
      Rows(i).EntireRow.Insert
    End If
  Next
  Application.ScreenUpdating = True
  .ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes).Name = "Table1" 'convert back to table
  End With
End Sub

 

[leap out]

really awesome !
million thanks for your assistance .

 

[Legacy 143009]

I am glad it did work! Thanks for the feedback