macro auto fill for each part

[Mussa]

I would repeat autofilling the item in column A for each part locate before TOTAL row . some parts contian one item , so no need repeat the item .
I would do that by macro please.
before

ITEM.xlsm
	A	B	C	D
1	DEL NO	BATCH NO	TTL	TT1
2	CC	CC-1	SS-1	TRU
3		CC-1	SS-1	LTR
4		CC-2	SS-2	FG
5	TOTAL
6	CCN	CC-1	SS-1	TRR
7		CC-1	SS-1	LTR
8	TOTAL
9	CD	CS-1	LL-1	RRL
10		CS-2	LL-2	TTY
11		CS-3	LL-3	MMW
12		CS-4	LL-4	NNW
13	TOTAL
14	CCL	CC-2	SS-2	LTR
15	TOTAL
16	CCM	CC-3	SS-3	LTR
17	TOTAL
data

after

ITEM.xlsm
	A	B	C	D
1	DEL NO	BATCH NO	TTL	TT1
2	CC	CC-1	SS-1	TRU
3	CC	CC-1	SS-1	LTR
4	CC	CC-2	SS-2	FG
5	TOTAL
6	CCN	CC-1	SS-1	TRR
7	CCN	CC-1	SS-1	LTR
8	TOTAL
9	CD	CS-1	LL-1	RRL
10	CD	CS-2	LL-2	TTY
11	CD	CS-3	LL-3	MMW
12	CD	CS-4	LL-4	NNW
13	TOTAL
14	CCL	CC-2	SS-2	LTR
15	TOTAL
16	CCM	CC-3	SS-3	LTR
17	TOTAL
data

 

[Marc L]

According to your attachment a VBA demonstration for starters :​

Sub Demo1()
        Dim Ra As Range
    With Sheets("Data").UsedRange.Columns(1)
        If Application.CountBlank(.Cells) = 0 Then Exit Sub
        Application.ScreenUpdating = False
        For Each Ra In .SpecialCells(4).Areas:  Ra(0).Copy Ra:  Next
    End With
        Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

You could try this with a copy of your data.

Sub FillBlanks()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

 

[Marc L]

​

The issue using a formula is the cell formatting is not copied if this is not within an Excel table …​

 

[Peter_SSs]

​

The issue using a formula is the cell formatting is not copied if this is not within an Excel table …​

Yes, I am aware of that. The op can decide if that is relevant/important.

 

[Mussa]

the codes are perfect. just question , how can I implement your codes for multiple sheets with the same structure,please?

 

[Marc L]

Why the important information is not in the initial post ?‼​

​

If all the worksheets have the same structure my VBA demonstration revamped :​

Sub Demo1r()
         Dim Ws As Worksheet, Ra As Range
    For Each Ws In Worksheets
        With Ws.UsedRange.Columns(1)
            If Application.CountBlank(.Cells) Then For Each Ra In .SpecialCells(4).Areas:  Ra(0).Copy Ra:  Next
        End With
    Next
End Sub

 

[Mussa]

Why the important information is not in the initial post ?‼

my apologies ! you're right . actually current case just I have one sheet , but I tought maybe I have many sheets in the future so I don't want come back to mod your code . that's it .
thanks very much for your time