Trying to delete a range using  .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete not working

Copper Contributor



I've inserted a column J into a macro that will put a 2 on each line when the current period and year, plus the previous year and period are a selected value in Excel and this works well:


 ActiveCell.FormulaR1C1 = _
 "=IF(AND(RC[-7]=" & CurrentPeriodYear & ",RC[-6]=" & CurrentPeriod & ",RC[-1]=" & Chr(34) & Office & Chr(34) & "),2,IF(AND(RC[-7]=" & LastPeriodYear & ",RC[-6]=" & LastPeriod & ",RC[-1]=" & Chr(34) & Office & Chr(34) & "),2,1))"
Selection.AutoFill Destination:=Range("J2:J" & lastrow)


Then I try to delete all the lines with a 2 on:


With ActiveSheet.Range("J2:J" & lastrow)
 .Replace "2", True, xlWhole
 .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
 End With


It fails on the line: 

 .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete


I've checked Lastrow and that is being calculated correctly.


And I can't see why. I have used this code before elsewhere. Could you help please - thank you



1 Reply
After looking at this for ages .. I tried copying the column J with the formula in and pasting it as values:
Application.CutCopyMode = False
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

then running the above and its worked fine. Thank you for your help though