Why Is This Happening?

[NeedsHelp2]

Over the past week or so I've been creating a spreadsheet with various userforms and approximatly 6 worksheets.

Everything seems to have been working fine until I added the code below to an option button. As soon as I added the code one of the userforms took 20 mins to open.

Thankfully I do regular backups so was able to go back to the last version.

I added the same piece of code and once again the same userform seemed to give up on me. I did not even try to get the code below to run and am at a loss to understand why the userform is acting this way.

Is anyone able to shed any light on this?

Private Sub OptionButton15_Click()
Dim SourceBook As Workbook
Dim FileToOpen As Variant
Dim ans
Dim LastRow As Long
Dim CopyRange As Range


If MsgBox("This will update all LookUp Tables, do you REALLY want to continue?", vbOKCancel) = vbCancel Then

   Cancel = True

   Exit Sub
Else

Unload Me

Sheets("LookUps").Select
Rows("2:65536").Select
   Selection.Delete Shift:=xlUp


On Error GoTo Errorhandler:
Set SourceBook = ActiveWorkbook

Application.ScreenUpdating = False

FileToOpen = Application.GetOpenFilename("XLS Files (*.xls), *.xls", , "Open a xls file")
If FileToOpen = False Then
MsgBox "stopped by user"
Else
ans = MsgBox("The following file will now open -" & vbCr & _
FileToOpen & ". Is this correct?", vbYesNo + vbDefaultButton2)
If ans = vbYes Then

Workbooks.Open FileToOpen

'------------------------------
    LastRow = ActiveSheet.Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row
    '------------------------------
    LastCol = ActiveSheet.Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByColumns).Column
    '------------------------------
    Set CopyRange = ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastCol))
    CopyRange.Copy Destination:=SourceBook.Sheets("LookUps").Range("A1")

ActiveWindow.Close

Else
Exit Sub

Errorhandler:

MsgBox ("Selection cancelled")

End If
End If
End If

Application.ScreenUpdating = True

MsgBox "Completed!"

End Sub

 

[Oorang]

I see several things that could be the culprit. The best thing to do is to try stepping through the code one line at a time and just see where it bogs. To do this just press F8 in the VBE.

 

[Norie]

Do you really want to delete 65535 rows here?

Sheets("LookUps").Select 
Rows("2:65536").Select 
   Selection.Delete Shift:=xlUp

Does this work any better?

Sheets("LookUps").Rows("2:65536").ClearContents

 

[Oorang]

Almost brought that up myself Slowdown could be where the workbook is being opened too.

 

[NeedsHelp2]

Norie, had already tried yoyr suggestion but made no difference.

Just to clarify, the userform that is being slowed down has nothing to do with the code that seems to be affecting it?

 

[Oorang]

You need to step through the code that is being slowed down.