Forum Discussion

Ros_123's avatar
Ros_123
Copper Contributor
Jul 25, 2023

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

Afternoon

 

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))"
Range("J2").Select
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

 

Ros

Macros and VBA
  • Ros_123's avatar
    Ros_123
    Copper Contributor
    Jul 25, 2023
    After looking at this for ages .. I tried copying the column J with the formula in and pasting it as values:
    Columns("J:J").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

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

Resources