specialcells issues

[bobaftt]

I seem to be having trouble with this line of code. Using the specialcells method deletes all the rows in the spreadsheet. This works most of the time but every now and again for large spreadsheets it doesn't work. I was wondering if any of you might have any suggestions?

'formulas to combine multiple entries on payfile
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Range("D2:D" & LastRow).FormulaR1C1 = "=IF(RC[-2]=R[-1]C[-2],RC[-1]+R[-1]C,RC[-1])"
    Range("E2:E" & LastRow).FormulaR1C1 = "=IF(RC[-3]=R[1]C[-3],"" "",""x"")"


'remove formulas
   Sheets("Payfile").Range("D2:D" & LastRow).Value = Sheets("Payfile").Range("D2:D" & LastRow).Value
   Sheets("Payfile").Range("E2:E" & LastRow).Value = Sheets("Payfile").Range("E2:E" & LastRow).Value

LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
'delete duplicates
 'Delete rows with blank cells
    
    Columns("E:E").Select
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

On Error Resume Next
    [E2:E65536].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0

[shg]

If SpecialCells finds a range with more than 8192 areas (non-contiguous subranges), it kindly returns the entire range, which is bad behavior.

So you need to write a loop that deletes in 16,000-row groups, bottom to top.

[bobaftt]

Thanks! That explains why it only happens on larger files. Do you happen to have any examples where you have used specialcells on 16000 line chunks of a spreadsheet?