Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel vba

ResolvedVersion 365
Edited on Thu 15 May 2025, 16:37

EXCEL VBA

Hi there,

I have a master tab in my spreadsheet that acts as my 'template'.

In cell A1 I have a data validation list with all potential values in a drop down. When I select a value in cell A1, my formularised 'template' dynamically updates.

This is a very formula heavy process (there are many calculations that are powered either directly or indirectly from A1). The end result is that I have 36 'results' at the bottom of my template.

Since I have roughly 60-70 possible values in A1, I wanted to find a way to extract all results into a single tab (i.e 36 * 70 = 2520).

I have experimented with VBA for this, and while I can get it to work, running the macros is extremely time consuming as they are very slow.

Would you have a suggestion about how I could either speed this up, or an alternative method to use?

I have tried DataTables, but this effectively crashes my workbook due to the large amount of calculations taking place.

Looking forward to your response,

Cian

RE: EXCEL VBA

Hello Cian,

Thank you for your interesting question.

If your VBA code already works, albeit slowly, you could try the following additional code to speed it up:

Disable screen updating, events, and automatic calculation while the macro runs:

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Or only calculate when needed (after setting the value in A1):

Application.Calculate

While not having seen your code, here is a macro which might run a bit faster than the one you wrote:

Sub ExtractAllResults()
Dim wsTemplate As Worksheet
Dim wsOutput As Worksheet
Dim inputValues As Variant
Dim resultRange As Range
Dim outputData() As Variant
Dim i As Long, j As Long
Dim numInputs As Long
Dim numResults As Long

Set wsTemplate = ThisWorkbook.Sheets("Master") ' Your template sheet
Set wsOutput = ThisWorkbook.Sheets("Results") ' Output sheet
Set resultRange = wsTemplate.Range("A100:A135") ' Adjust to match your 36 output cells

inputValues = wsTemplate.Range("ValidationList") ' Named range with values for A1
numInputs = UBound(inputValues, 1)
numResults = resultRange.Rows.Count

ReDim outputData(1 To numInputs * numResults, 1 To 2)

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

For i = 1 To numInputs
wsTemplate.Range("A1").Value = inputValues(i, 1)
DoEvents ' Allows Excel to recalculate if needed

For j = 1 To numResults
outputData((i - 1) * numResults + j, 1) = inputValues(i, 1) ' Input value
outputData((i - 1) * numResults + j, 2) = resultRange.Cells(j, 1).Value ' Result
Next j
Next i

' Output the data in one shot (much faster)
wsOutput.Range("A1").Resize(UBound(outputData), 2).Value = outputData

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

MsgBox "Extraction complete!"
End Sub

Please let us know if this worked for you.

Kind regards
Marius Barnard
STL

RE: EXCEL VBA

Thanks for your suggestion Marius, those tweaks definitely helped to speed up my macros.

Cian

Excel tip:

Selecting blank cells within a range of data

Select the range of data which includes the blank cells that you would like to select. Press the F5 key, this will take you to the GoTo dialogue box where you can click on Special and then select Blanks.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.