Results 1 to 16 of 16

VBA Code running very slow to seperate 250 rows into 5 categories

Threaded View

  1. #1
    Registered User
    Join Date
    09-29-2015
    Location
    Gainesville, FL
    MS-Off Ver
    2013
    Posts
    7

    VBA Code running very slow to seperate 250 rows into 5 categories

    The basics of this code are to examine a spreadsheet by multiple criteria and separate it into 5 categories. Then copy 4 of the categories and only certain column of data to another spreadsheet sorting it into 4 segregated areas on the spreadsheet, inserting rows as needed. It runs as it is but takes up to a minute to make it through the 250 rows and is inserting multiple blank rows and over righting other rows. What am I missing please


    Private Sub ReportMakeReady_Click()
    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
    
    Unload Me
    Dim FRow As Long
    Dim LRow As Long
    Dim ColAPLast1 As Long
    Dim ColAPLast2 As Long
    Dim APValues As Variant
    Dim MRValues As Variant
    Dim AP As Worksheet
    Dim MR As Worksheet
    
    Set AP = Worksheets("apartments")
    Set MR = Worksheets("Make Ready")
        
    Dim CRented As Long, CRemodel As Long, CAdmin As Long, CRNMI As Long, CStatus As Long, CUnit As Long
    Dim CTurnNotes As Long, CUnitNotes As Long, CFinal As Long, CCabinets As Long, CFridge As Long, CRange As Long
    Dim CAC As Long, CTub As Long, CCLean As Long, CPaint As Long, CVynal As Long, CUporDown As Long, CITV As Long
    Dim CCarpet As Long, CMaint As Long, CMoveIn As Long, CFloorPlan As Long, CMoveOutRemodel As Long, CTurn As Long
        
    Dim MRentedMain As Long, MRented1Bed As Long, MRented2Bed As Long
    Dim MAvailMain As Long, MAvail1Bed As Long, MAvail2Bed As Long
    Dim MNotAvailMain As Long, MNotAvail1Bed As Long, MNotAvail2Bed As Long
    Dim MNoticeMain As Long, MNotice1Bed As Long, MNotice2Bed As Long, MEndLine As Long
        
    Dim MUnit As Long, MFloorPlan As Long, MUporDown As Long, MRemodel As Long
    Dim MMoveOutRemodel As Long, MMoveIn As Long, MStatus As Long, MMaint As Long
    Dim MCarpet As Long, MVynal As Long, MPaint As Long, MClean As Long, MAC As Long, MFridge As Long
    Dim MRange As Long, MTub As Long, MUnitNotes As Long, MTurnNotes As Long, MFinal As Long, MCabinets As Long
    
                With Worksheets("apartments")
                    ColAPLast1 = .Cells(1, Columns.Count).End(xlToLeft).Column
                    With .Range(.Cells(1, 1), .Cells(1, ColAPLast1))
                        CRented = .Find("Occupied").Column
                        CRNMI = .Find("RNMI").Column
                        CAdmin = .Find("Admin").Column
                        CTurn = .Find("Turned").Column
                        CITV = .Find("ITV").Column
                        CFloorPlan = .Find("Floor Plan").Column
                        CUnit = .Find("Apartment").Column
                        CUporDown = .Find("Up or Down").Column
                        CRemodel = .Find("Remodel").Column
                        CMoveOutRemodel = .Find("MO / Remodel").Column
                        CMoveIn = .Find("Move In").Column
                        CStatus = .Find("Status").Column
                        CMaint = .Find("Maintenance").Column
                        CCarpet = .Find("Carpet").Column
                        CVynal = .Find("Linoleum").Column
                        CPaint = .Find("Painted").Column
                        CCLean = .Find("Clean").Column
                        CAC = .Find("AC").Column
                        CFridge = .Find("Fridge").Column
                        CRange = .Find("Range").Column
                        CTub = .Find("Tub").Column
                        CCabinets = .Find("Cabinets").Column
                        CUnitNotes = .Find("Unit Notes").Column
                        CFinal = .Find("Final Inspec").Column
                        CTurnNotes = .Find("Turn Notes").Column
                    End With
                End With
        
                With Worksheets("Make Ready")
                    ColAPLast2 = .Cells(1, Columns.Count).End(xlToLeft).Column
                    With .Range(.Cells(1, 1), .Cells(1, ColAPLast2))
                        MUnit = .Find("Unit").Column
                        MFloorPlan = .Find("Floor").Column
                        MUporDown = .Find("UpDown").Column
                        MRemodel = .Find("Remodel").Column
                        MMoveOutRemodel = .Find("Mo/Re Date").Column
                        MMoveIn = .Find("Move in").Column
                        MStatus = .Find("Status").Column
                        MMaint = .Find("Maint").Column
                        MCarpet = .Find("Carpet").Column
                        MVynal = .Find("Vynal").Column
                        MPaint = .Find("Paint").Column
                        MClean = .Find("Clean").Column
                        MAC = .Find("AC").Column
                        MFridge = .Find("Fridge").Column
                        MRange = .Find("Range").Column
                        MTub = .Find("Tub").Column
                        MCabinets = .Find("Cabinets").Column
                        MUnitNotes = .Find("Unit Notes").Column
                        MFinal = .Find("Final").Column
                        MTurnNotes = .Find("Turn Notes").Column
                    End With
                End With
                
                With MR.Range("A1:A250")
                    MRentedMain = .Find("RentedMain").Row
                    MRented1Bed = .Find("Rented1Bed").Row
                    MRented2Bed = .Find("Rented2Bed").Row
                    MAvailMain = .Find("AvailableMain").Row
                    MAvail1Bed = .Find("Available1Bed").Row
                    MAvail2Bed = .Find("Available2Bed").Row
                    MNotAvailMain = .Find("NotAvailableMain").Row
                    MNotAvail1Bed = .Find("NotAvailable1Bed").Row
                    MNotAvail2Bed = .Find("NotAvailable2Bed").Row
                    MNoticeMain = .Find("NoticeMain").Row
                    MNotice1Bed = .Find("Notice1Bed").Row
                    MNotice2Bed = .Find("Notice2Bed").Row
                    MEndLine = .Find("EndLine").Row
                End With
                
                With Worksheets("apartments")
                    APValues = .Range(.Cells(1, 1), .Cells(250, ColAPLast1)).Value
                End With
                
                With Worksheets("Make Ready")
                    MRValues = .Range(.Cells(1, 1), .Cells(250, ColAPLast2)).Value
                End With
                
        For FRow = 2 To 250
        
            If APValues(FRow, CAdmin) = "" And APValues(FRow, CRNMI) = "" And APValues(FRow, CITV) = "" _
                And APValues(FRow, CTurn) = "" And APValues(FRow, CRented) = "" Then
                    If APValues(FRow, CFloorPlan) = "1x1 W/D" Or APValues(FRow, CFloorPlan) = "1x1" Then
                        LRow = ((MNotAvail2Bed - MNotAvail1Bed) - 2) + MNotAvail1Bed
                        MR.Cells(MNotAvail2Bed, 1).Offset(-1).EntireRow.Insert
                    Else: APValues(FRow, CFloorPlan) = "2x1"
                        LRow = ((MNoticeMain - MNotAvail2Bed) - 2) + MNotAvail2Bed
                        MR.Cells(MNoticeMain, 1).Offset(-1).EntireRow.Insert
                    End If
            ElseIf APValues(FRow, CAdmin) = "" And APValues(FRow, CRNMI) = "" And APValues(FRow, CITV) = "" _
                And APValues(FRow, CTurn) = "X" And APValues(FRow, CRented) = "" Then
                    If APValues(FRow, CFloorPlan) = "1x1 W/D" Or APValues(FRow, CFloorPlan) = "1x1" Then
                        LRow = ((MAvail2Bed - MAvail1Bed) - 2) + MAvail1Bed
                        MR.Cells(MAvail2Bed, 1).Offset(-1).EntireRow.Insert
                    Else: APValues(FRow, CFloorPlan) = "2x1"
                        LRow = ((MNotAvailMain - MAvail2Bed) - 2) + MAvail2Bed
                        MR.Cells(MNotAvailMain, 1).Offset(-1).EntireRow.Insert
                    End If
            ElseIf APValues(FRow, CAdmin) = "" And APValues(FRow, CRNMI) = "" And APValues(FRow, CITV) = "X" _
                And APValues(FRow, CTurn) = "" And APValues(FRow, CRented) = "" Then
                    If APValues(FRow, CFloorPlan) = "1x1 W/D" Or APValues(FRow, CFloorPlan) = "1x1" Then
                        LRow = ((MNotice2Bed - MNotice1Bed) - 2) + MNotice1Bed
                        MR.Cells(MNotice2Bed, 1).Offset(-1).EntireRow.Insert
                    Else: APValues(FRow, CFloorPlan) = "2x1"
                        LRow = ((MEndLine - MNotice2Bed) - 2) + MNotice2Bed
                        MR.Cells(MEndLine, 1).Offset(-1).EntireRow.Insert
                    End If
            ElseIf APValues(FRow, CAdmin) = "" And APValues(FRow, CRNMI) = "X" And APValues(FRow, CITV) = "" _
                And APValues(FRow, CTurn) = "" And APValues(FRow, CRented) = "" Then
                    If APValues(FRow, CFloorPlan) = "1x1 W/D" Or APValues(FRow, CFloorPlan) = "1x1" Then
                        LRow = ((MRented2Bed - MRented1Bed) - 2) + MRented1Bed
                        MR.Cells(MRented2Bed, 1).Offset(-1).EntireRow.Insert
                    Else: APValues(FRow, CFloorPlan) = "2x1"
                        LRow = ((MAvailMain - MRented2Bed) - 2) + MRented2Bed
                        MR.Cells(MAvailMain, 1).Offset(-1).EntireRow.Insert
                    End If
            End If
                
                If LRow = 0 Then
                Else
                    MR.Cells(LRow, MUnit).Value = AP.Cells(FRow, CUnit).Value
                    MR.Cells(LRow, MFloorPlan).Value = AP.Cells(FRow, CFloorPlan).Value
                    MR.Cells(LRow, MUporDown).Value = AP.Cells(FRow, CUporDown).Value
                    MR.Cells(LRow, MRemodel).Value = AP.Cells(FRow, CRemodel).Value
                    MR.Cells(LRow, MMoveOutRemodel).Value = AP.Cells(FRow, CMoveOutRemodel).Value
                    MR.Cells(LRow, MMoveIn).Value = AP.Cells(FRow, CMoveIn).Value
                    MR.Cells(LRow, MStatus).Value = AP.Cells(FRow, CStatus).Value
                    MR.Cells(LRow, MMaint).Value = AP.Cells(FRow, CMaint).Value
                    MR.Cells(LRow, MCarpet).Value = AP.Cells(FRow, CCarpet).Value
                    MR.Cells(LRow, MVynal).Value = AP.Cells(FRow, CVynal).Value
                    MR.Cells(LRow, MPaint).Value = AP.Cells(FRow, CPaint).Value
                    MR.Cells(LRow, MClean).Value = AP.Cells(FRow, CCLean).Value
                    MR.Cells(LRow, MAC).Value = AP.Cells(FRow, CAC).Value
                    MR.Cells(LRow, MFridge).Value = AP.Cells(FRow, CFridge).Value
                    MR.Cells(LRow, MRange).Value = AP.Cells(FRow, CRange).Value
                    MR.Cells(LRow, MTub).Value = AP.Cells(FRow, CTub).Value
                    MR.Cells(LRow, MCabinets).Value = AP.Cells(FRow, CCabinets).Value
                    MR.Cells(LRow, MUnitNotes).Value = AP.Cells(FRow, CUnitNotes).Value
                    MR.Cells(LRow, MFinal).Value = AP.Cells(FRow, CFinal).Value
                    MR.Cells(LRow, MTurnNotes).Value = AP.Cells(FRow, CTurnNotes).Value
                    LRow = 0
                End If
        
        Next FRow
        
        Worksheets("Make Ready").Activate
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        Application.DisplayStatusBar = True
        Application.EnableEvents = True
        ActiveSheet.DisplayPageBreaks = True
    
    End Sub
    Attached Files Attached Files
    Last edited by Hareborn; 09-29-2015 at 02:57 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. code running to slow and to long
    By Pilot5000 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-03-2015, 05:39 AM
  2. Slow running Code
    By Ausadian in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-25-2015, 04:41 PM
  3. Slow running code
    By phil2006 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-13-2013, 08:02 AM
  4. vba code running too slow
    By hitsujicute in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-31-2013, 06:00 PM
  5. VBA Code running very slow. Need help
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-21-2013, 02:13 PM
  6. Slow running code
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-06-2008, 12:49 PM
  7. Code running slow
    By lou031205 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-08-2007, 12:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1