[RESOLVED] Lookup deletion rows problems excel crashing

[gphillips]

I have some code below which is crashing.

I need to delete items in sheet 1 not shown in sheet 2 of a lareg spreasheet 35000 plus.

Can someone clean up my code so stops crashing and ist quicker. It seems to work for small number of rows, but not for large number of rows.


There must be something simple wrong. An explannation of the code would be great as well.

Sub SelectedDesigns2()
 
Dim sheetcount As Integer
Dim FileLength As Long
Dim sheetname As String
 
sheetcount = Worksheets.Count
 
If sheetcount < 2 Then
MsgBox ("Muppet! You do not have a selected designs sheet.")
Else
 
'Activate sheet
Worksheets(2).Activate
 
sheetname = ActiveSheet.Name
 
'Activate sheet
Worksheets(1).Activate
 
'Find how many codes there are
FileLength = ActiveSheet.UsedRange.Rows.Count
 
    Range("B:B").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(VLOOKUP(RC[-1],'" & sheetname & "'!C1:C1,1,FALSE)),""n"",""y"")"
    
    Selection.AutoFill Destination:=Range("B2:B" & FileLength)
End If
 
Range("B:B").Select
Selection.Cells.Copy
Range("b2").PasteSpecial xlPasteValues
 
 
Range("B2").Activate
 
 
 
For i = 2 To FileLength
If ActiveCell.Text = "y" Then
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.EntireRow.Delete
End If
Next i
 
Range("B:B").Delete
 
 
End Sub

Boris