Type Mismatch When deleting Duplicate Headers

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,665
Office Version
  1. 2019
Platform
  1. Windows
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

Code:
 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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows
Very strange, in my excel this code can run normally
You can try changing:
VBA Code:
. Range ("a" & I). [COLOR=rgb(184, 49, 47)]value[/COLOR]
to:
VBA Code:
. Range ("a" & I). [COLOR=rgb(184, 49, 47)]text[/COLOR]

Maybe you should also check the format(number) of column A
 
Last edited:

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows
In addition, this is the data I use to test the code
test.xlsm
AB
1
2A
3B
4C
5D
6Reference
7F
8G
9Reference
10I
11J
12K
13Reference
14M
15N
16Reference
17P
Data Import
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,665
Office Version
  1. 2019
Platform
  1. Windows
I changed .value to .text and it now works
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,899
Office Version
  1. 365
Platform
  1. Windows
@hnsd24_CN
If you want to apply your own format to vba code, use the RICH code tags.
1603540308601.png
 

Watch MrExcel Video

Forum statistics

Threads
1,114,337
Messages
5,547,357
Members
410,786
Latest member
dworkin
Top