IF and then

Hi Dayu,

I have a feeling, that the file you have is different from the one you sent Luke.

Do you have the date in column B?

If not you must change the code.

Cells(lRow,2) is looking for the date in column 2.

If Not Month(Cells(lrow, 2).Value) = 11 Then
Cells(lrow, 2).EntireRow.Delete
End If



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Hi Dayu,

I have spent some time looking at your data.

The file you sent Luke was not the same as the data you sent me. In the file you sent Luke there are dates in column B. Excel cannot recognise column B in the data you sent me as dates. You have hidden characters in the column and it is text strings not dates. This explain why you got the error 13 Mismatch.

I couldn't get your code (Text to Columns) to work. I have rerecorded the Text to Column and the code works. The code clean the hidden characters, Format to dates and delete the unwanted rows.

Copy the data from the CSV file and paste them in a XLSX workbook and run both code.





Sub ToColumns()

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1)), TrailingMinusNumbers:=True
End Sub
Sub RemoveRows()

Dim lRows As Long
Dim lRow As Long

lRows = Range("a2").CurrentRegion.Rows.Count

For lRow = lRows To 2 Step -1

Cells(lRow, 2).Value = Application.WorksheetFunction.Clean(Cells(lRow, 2).Value)
Next lRow


For lRow = lRows To 2 Step -1
Cells(lRow, 2).Value = Format(WorksheetFunction.Substitute(Cells(lRow, 2), ".", "/"), "dd/mm/yyyy")
Next lRow



For lRow = lRows To 2 Step -1


If Not Cells(lRow, 2).Value Like "*/12/*" Then
Cells(lRow, 2).EntireRow.Delete
End If


If Cells(lRow, 8).Value <> "" And Cells(lRow, 7) = "" Then
Cells(lRow, 2).EntireRow.Delete
End If
Next lRow
End Sub





Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector