Avoiding Excessive "Selects"

[PeterTaylor]

Dear All,
I am using excel 2007 with Vista home 64 bit. I have the following code which steps through a list of excel files opening each counting the number of records and then reporting the number of records to another excel file.

The macro works fine but I have heard that use of the "select" statement will slow down a macro. I was wondering if someone could point me towards a different strategy for that would have same outcome (but faster) that I have achieved.

Sub Count_Records()
'
'    Application.DisplayAlerts = False
    Dim mylist As String, myFilename As String, aRow As Integer, bCol As Integer, TestBlank As Integer, zRow As Integer, _
    ZCol As Integer, startData As Integer, Enddata As Integer, RandomRow As Integer, myWindowname As String, _
    Varcount As Integer, xRow As Integer, xCol As Integer
    
    
    
        Workbooks.Open Filename:="G:\Records.xlsm"
        Workbooks.Open Filename:="K:\openfileworking.xlsm"
        Workbooks.Open Filename:="K:\Collar_import_log_file.xlsm"
        
            aRow = 2
            zRow = 1
            ZCol = 1
            xRow = 1
            xCol = 1
        Sheets("imports").Select
    ' Start loop
Do
'On Error GoTo 0
    ' Define values to mylist and myFilename for current pass of Do - Loop cycle
        mylist = Cells(aRow, 1).Value
        myFilename = Cells(aRow, 2).Value
        myWindowname = Cells(aRow, 3).Value
    ' check length of current mylist value,for a zero length
        If Len(mylist) = 0 Then
            ' in case of stray blanks in unsorted data, check next 10 rows
                If TestBlank < 10 Then
                    TestBlank = TestBlank + 1
                    ' kick back to loop start
                    ' GoTo NoError:
        Else
            MsgBox "End of File Encountered The Procedure will now exit"
                Windows("Records.xlsm").Activate
                ActiveWorkbook.Save
                
            Application.DisplayAlerts = True
Exit Do
                End If
        End If
        ' a valid entry needed to get here so reset testblank to 0
        TestBlank = 0
        ' for a non zero length value of mylist, report current value to user and attempt to open file
        


        'MsgBox "Opening " & mylist
        Workbooks.Open mylist
        Cells(1, 1).Select
        Selection.End(xlDown).Select
        startData = ActiveCell.Row
        ActiveWorkbook.Close
        Windows("Records.xlsm").Activate
        Cells(zRow, 1).Select
        ActiveCell.Value = mylist
        Cells(zRow, 2).Select
        ActiveCell.Value = startData
        zRow = zRow + 1
        aRow = aRow + 1
        Windows("Collar_import_log_file.xlsm").Activate
        
 Loop

End Sub

Thank you
Peter

 

[CWatts]

I'm far from a VBA guru and my code is clunky, but I've found in my experience that this helps speed things up for me .

Start code with:

Application.ScreenUpdating = False
Application.Calculation = xlManual

Set things back:

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

 

[Cindy Ellis]

The concept of reducing the "selects" is easy to implement in MOST (but not all) cases when the code is something like:

Cells(1,1).Select
Selection.Value = "abc"

You can simplify the code and speed up the macro by using

Cells(1,1).Value = "abc"

In other words, you pull out the ".select" on the first line and the "selection" on the 2nd line, and combine them into one.
The code you have has a little more to it than that, so you have to think about what you're really trying to do. You can combine the first three lines of code into

startData = Cells(1, 1).End(xlDown).Row

Similarly,

Cells(zrow, 1).Select
ActiveCell.Value = mylist

turns into

Cells(zrow, 1).Value = mylist

From that, you should be able to fix the remaining "select" statement yourself

Hope that helps,
Cindy

 

[PeterTaylor]

Thank you for you help
Regards
Peter