none
Help optimizing this Excel 2003 vba code to run quicker in Excel 2010 RRS feed

  • Question

  • I have a macro that went from running in 1-2 minutes in 2003 to taking 35-40 minutes in 2010.  I have been trying to figure out what is causing the slow down.  I think I have narrorwed it to this section of code.
    Public Property Get StartRow(sheetName, TextValue, Optional theOccurrence As Integer)
        Dim Count As Integer
        Set c = Sheets(sheetName).Cells.Find(What:=TextValue, After:=Selection.SpecialCells(xlCellTypeLastCell), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        Count = theOccurrence
        Do Until Count < 2
            Set c = Cells.FindNext(After:=Cells(c.Row, c.Column))
            Count = Count - 1
        Loop
        If c Is Nothing Then
            theStartRow = "Nothing"
        Else
            theStartRow = c.Row
        End If
        Set c = Nothing
        StartRow = theStartRow
    End Property

    Public Property Get EndRow(sheetName, TextValue, Optional theOccurrence As Integer)
        Set c = Sheets(sheetName).Cells.Find(What:=TextValue, After:=Selection.SpecialCells(xlCellTypeLastCell), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        Count = theOccurrence
        Do Until Count < 2
            Set c = Cells.FindNext(After:=Cells(c.Row, c.Column))
            Count = Count - 1
        Loop
        If c Is Nothing Then
            theEndRow = "Nothing"
        Else
            i = c.Row
            j = c.Column
            Do
                i = i + 1
            Loop Until Sheets(sheetName).Cells(i, j) = ""
            theEndRow = i - 1
        End If
        Set c = Nothing
        EndRow = theEndRow
    End Property
    I have similar code to get the StartCol and EndCol.  Does anyone have a more efficent way to get the begining and ending row and column of the data on the sheet?


    Thanks,
     

    Casey


    Thursday, October 16, 2014 6:04 PM

Answers

  • Re:  using find method in vba

    When you use:  Set c = Sheets(sheetName).Cells.Find(...
       You are telling Excel to search the entire sheet by using ".Cells"
       You should be able to speed this up by specifying a specific range to search.
       This might work...
    Set c = Sheets(sheetName).UsedRange.Find( ...

    Also, Excel already uses "Count" in VBA.  Create another variable name.
    And I don't see where "C" has been declared

    Better way?
    If you have a specific column or row to search such as Column("B") then the following is pretty swift...
       Dim Rw As Long
       Rw = Sheets(1).Cells(Sheets(1).Rows.Count, 2).End(xlUp).Row
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    • Marked as answer by Casey Marx Wednesday, October 22, 2014 7:58 PM
    • Edited by James Cone Monday, October 31, 2016 7:10 PM
    Friday, October 17, 2014 1:22 AM