Speeding up adding validation lists to a spread range of cells using vba

Question

I am working with a fairly small worksheet that has been developed by someone else. In this worksheet I have approx. 500 rows and some 100 columns (these values change dynamically).

The document adds validation lists to some cells based on a named range in another worksheet in the same workbook. This currently works, but very slowly.

The cells I would like to target are cells that on the same row, in column A, have a certain value. The cells should also have a specific name in its "header".

Currently, I am using a find statement to find all correct columns, and then for each of those columns I check the value in column A for the correct one, and if it is, I add the range.

Now to the question; How can I speed this up? When the sheet is at its largest it takes over a minute to complete the code, and since that happens when you open the sheet, people using the sheet are complaining. :)

Application.ScreenUpdating = False
Application.EnableEvents = False

Sheets(A).Activate
Sheets(A).Unprotect Password:=Str_SheetPassword

'Get each data ranges
Set Rg_TitleRange = ...
Set Rg_dataRange = ...

'Loop on each column that contains the keyword name
Set Rg_ActionFound = Rg_TitleRange.Find(Str_ColName, LookIn:=xlFormulas, _
    lookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True)
If Not Rg_ActionFound Is Nothing Then
    'Loop on each action column
    Do
        'For each data row, update the cells with validation list
        For Int_RowIndex = 0 To Rg_dataRange.Rows.Count - 1
            'Change cells wich are at the intersection of test definition row and action name column.
            If Rg_dataRange(Int_RowIndex, 1) = Str_RowName Then
                Set Val_ActionValidationList = Rg_dataRange(Int_RowIndex, Rg_ActionFound.Column).Validation
                Val_ActionValidationList.Delete
                Rg_dataRange(Int_RowIndex, Rg_ActionFound.Column).Validation.Add _
                    Type:=xlValidateList, Formula1:=("=" + Str_ValidationList)
            End If
        Next
        'Loop end actions
        Int_PreviousActionFoundColumn = Rg_ActionFound.Column
        Set Rg_ActionFound =  Rg_TitleRange.Find(CommonDataAndFunctionMod.Str_ActionNameRowLabel, Rg_ActionFound,     LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True)
    Loop While Rg_ActionFound.Column > Int_PreviousActionFoundColumn
End If

Application.ScreenUpdating = True
Application.EnableEvents = True

I have tested to just comment out the row where the validation is added, so I'm fairly sure that row is the time consumer (mostly). I would take any suggestions.

Thank you in advance!

Try temporarily disabling calculations too (Application.Calculation = xlCalculationManual). — GSerg
Thanks. I tried this and it does not have any major impact on computational time, though perhaps a small improvement. — JLa
Is this in a Workbook_Open command? Have you tried using OptionExplicit at the top of your module to require variable declaration and then declaring your variables to the "tightest" possible type? You use Int, Rg and Val to prefix your variable names but haven't defined them as those types (although I'm not sure what data type Val equates to). Your Set Rg_TitleRange = ... and Set Rg_dataRange = ... don't compile which makes it difficult to debug the rest of your code. Could you edit it? — Mark Fitzgerald
This is in a separate function called by the Worksheet_activate function of the sheet. The objects are in fact declared as Int, Range and Validation (as in a validation object) at the beginning of the code. However, I had a feeling you guys didn't want to go through my entire project so I tried to give you the "issue area". Rg_TitleRange and Rg_dataRange simply gets the area of cells we are worried about, maybe you could set them to wherever you place your data in your test environment? — JLa

JLa JLa · Accepted Answer · 2014-08-26T07:20:49

After some tries I ended up redoing the code so that this routine is run on certain other events instead, hence removing the loading time on start up. The validations are updated only when needed now.

Thank you all for your suggestions!

Speeding up adding validation lists to a spread range of cells using vba

2 Answers