Type Mismatch When deleting Duplicate Headers

[howard]

I have headers in Col A with text "Reference"

I want to delete the rows containing text "Reference" except the first row containing this


However when run my macro, I get Type mismatch

 Sub Del_Unwanted()
Dim LR As Long, i As Long
With Sheets("Data Import")

LR = .Range("A" & .Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If .Range("A" & i).Value = "Reference" Then .Range("A" & i).EntireRow.Delete
Next i

.UsedRange.EntireColumn.AutoFit
End With
Columns("L:L").ColumnWidth = 64.86
End Sub

It would be appreciated if someone could kindly amend my code

 

[hnsd24_CN]

Very strange, in my excel this code can run normally
You can try changing:

. Range ("a" & I). [COLOR=rgb(184, 49, 47)]value[/COLOR]

to:

. Range ("a" & I). [COLOR=rgb(184, 49, 47)]text[/COLOR]

Maybe you should also check the format（number） of column A

 

[hnsd24_CN]

In addition, this is the data I use to test the code

test.xlsm
	A	B
1
2	A
3	B
4	C
5	D
6	Reference
7	F
8	G
9	Reference
10	I
11	J
12	K
13	Reference
14	M
15	N
16	Reference
17	P
Data Import

 

[howard]

I changed .value to .text and it now works

 

[Peter_SSs]

@hnsd24_CN
If you want to apply your own format to vba code, use the RICH code tags.