Not sure why my simple copy rows is running so slowly

LukeAvedon

New Member
Joined
Apr 5, 2016
Messages
3
Hello,

Maybe someone here will be kind enough to help me.

I have a simple subroutine but it runs incredibly slowly.

I have one sheet CodeName "SellHack". For now there are about 700 Rows. About half of the records have "verified" listed on Column "O". I'm
trying to copy all of the rows that do NOT contain verified in column "O" (15).


When I run this code it takes about 5 minutes, which I find very strange. I have other subroutines that are even more poorly written by myself and don't have this problem.


Code:
Sub nonVerfiedEmailsNewSheet()


    Dim ws As Worksheet
    Dim wb As Workbook
    Dim i As Integer
    Dim wsRowCount As Integer
    
    Dim LastRow As Variant
    Dim rng As Range
    Dim rPlacementCell As Range
    Dim rFoundCell As Range
    
    'Call optimize code
    Call SetUp.OptimizeCode_Begin
    
               
    Set wb = ThisWorkbook
    i = wb.Worksheets.Count
    
        
    
        
    'Create a new sheet
    wb.Worksheets.Add After:=Worksheets(i)
    i = i + 1
        
    
    
    Set ws = wb.Worksheets(i)
    ws.Name = "All Non-Verified Emails"
    
    
    SellHack.Range("A1").EntireRow.Copy Destination:=ws.Range("A1")
   


    
    '**********Find all non-verfieid rows and copy*********
    
    
    'Find the last row of the sellhack sheet
    LastRow = SellHack.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    LastCol = SellHack.UsedRange.SpecialCells(xlCellTypeLastCell).Column
    
    
    'A simple loop that looks in each cell
    
    wsRowCount = 2
    For i = 2 To LastRow
        
        If LCase(Trim(SellHack.Cells(i, 15).Value)) <> "verified" Then
            With SellHack
                .Range(.Cells(i, 1), .Cells(i, LastCol)).Copy
            End With
                ws.Cells(wsRowCount, 1).PasteSpecial xlPasteValues
                wsRowCount = wsRowCount + 1
            End If
    Next i
        
    'Make the sheet readable
    ws.Columns.AutoFit
    
    Call SetUp.OptimizeCode_End
    
End Sub

I tried adding two other subs to turn off screenupdating, etc. but it's still incredibly slow.
Code:
Public CalcState As Long
Public EventState As Boolean
Public PageBreakState As Boolean


Sub OptimizeCode_Begin()


Application.ScreenUpdating = False


EventState = Application.EnableEvents
Application.EnableEvents = False


CalcState = Application.Calculation
Application.Calculation = xlCalculationManual


PageBreakState = ActiveSheet.DisplayPageBreaks
ActiveSheet.DisplayPageBreaks = False


End Sub


Sub OptimizeCode_End()


ActiveSheet.DisplayPageBreaks = PageBreakState
Application.Calculation = CalcState
Application.EnableEvents = EventState
Application.ScreenUpdating = True


End Sub

Thank you, any help is greatly appreciated.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

JimRau

Board Regular
Joined
Jul 17, 2014
Messages
66
Instead of reading the worksheet one row at a time what if you made a copy of the worksheet and deleted the rows that have "verified" in column O?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe this to replace ALL of your codes !
Code:
Sub nonVerfiedEmailsNewSheet()
Dim ws As Worksheet, rng As Range, lr As Long
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With
'Create a new sheet
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "All Non-Verified Emails"
lr = SellHack.Cells(Rows.Count, "A").End(xlUp).Row + 1
    With SellHack.Range("A2:O" & lr)
        .AutoFilter Field:=15, Criteria1:=""
        .SpecialCells(xlCellTypeVisible).Copy Sheets("All Non-Verified Emails").Range("A1")
        .AutoFilter
    End With
Sheets("All Non-Verified Emails").Columns.AutoFit
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

End Sub
 

LukeAvedon

New Member
Joined
Apr 5, 2016
Messages
3
Hello thanks for your help and the quick reply!

Looks like I don't understand AutoFilter at all. That's the trick. Thanks again.

--Luke
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
glad it helped...(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,892
Messages
5,598,693
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top