Ignore cell value

[Lauraswint]

I have a report in which if a certain value is in the cell I would like it to be ignored.

e.g column B if cell value _ Cha then ignore it
if value = anything else then complete action


This is the code currently

'machine
With Sheets("Sheet1")
    lr = .Cells(Rows.Count, "l").End(xlUp).Row
    .Range("l3:L" & lr).Copy Destination:=Sheets("Sheet2").Range("B6")
End With

 

[VoG]

Try like this

Dim LR As Long
With Sheets("Sheet1")
    LR = .Cells(Rows.Count, "L").End(xlUp).Row
    .Range("B1:B" & LR).AutoFilter field:=1, Criteria1:="<>_ Cha"
    .Range("L3:L" & LR).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Sheet2").Range("B6")
    .Range("B1:B" & LR).AutoFilter
End With

 

[Lauraswint]

Try like this

Dim LR As Long
With Sheets("Sheet1")
    LR = .Cells(Rows.Count, "L").End(xlUp).Row
    .Range("B1:B" & LR).AutoFilter field:=1, Criteria1:="<>_ Cha"
    .Range("L3:L" & LR).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Sheet2").Range("B6")
    .Range("B1:B" & LR).AutoFilter
End With

I have tried this but I am getting a compile error on the DIM LR As long

Says Duplicate declaration in current scope.

No idea what this means??? Arghhhhhh

 

[VoG]

Just delete that line of code.

 

[Lauraswint]

Progress but it has now moved to the first range statement with a runtime error 1004 Application defined or object defined error

Thank you for your help

 

[VoG]

This is what I tested without error

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L
1
2
3	_ Cha										1
4											2
5											3
6	_ Cha										4
7											5
8											6
9	_ Cha										7
Sheet1

Sub test()
Dim LR As Long
With Sheets("Sheet1")
    LR = .Cells(Rows.Count, "L").End(xlUp).Row
    .Range("B1:B" & LR).AutoFilter field:=1, Criteria1:="<>_ Cha"
    .Range("L3:L" & LR).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Sheet2").Range("B6")
    .Range("B1:B" & LR).AutoFilter
End With
End Sub

Result

Excel Workbook
	B
1
2
3
4
5
6	2
7	3
8	5
9	6
Sheet2