Look Who it Was Random video

Excel - Streamline Your Excel Calculations with This Clever VBA Trick - Episode 1140

1,961 views · Published 9 November 2009 · 4:54 · Indexed 23 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Streamline Your Excel Calculations with This Clever VBA Trick.

Welcome back to the MrExcel netcast, where we tackle all your Excel problems and find the most efficient solutions. In this episode, we're going to discuss how to avoid a loop in your Excel calculations. This is a common issue that many Excel users face when dealing with large amounts of data. But don't worry, we've got you covered!

During the Power Analyst Boot Camp, a participant asked a great question about how to optimize their VBA code. They had a worksheet with 750 rows of data and a complex formula in column H that would hide or unhide rows based on certain criteria. However, this calculation was taking a long time to run, even with attempts to speed it up. So, we had to find a better solution.

After some trial and error, we discovered that using the SpecialCells function was the key to speeding up the process. By changing the formula in column H to a number and using the SpecialCells function to only select cells with text, we were able to create a loop that ran much faster. In fact, it went from taking minutes to just one second! This is a game-changer for anyone dealing with large datasets in Excel.

So, why did we choose to go with a number instead of the original formula? Well, by using a number, we were able to use the Go To Special function to only select cells with text, which greatly reduced the number of iterations needed in the loop. This is a clever and efficient way to solve the problem of a slow loop in your Excel calculations.

I hope this tip helps you save time and frustration in your Excel projects. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. Thanks for watching and we'll see you next time on the MrExcel netcast!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Introduction
(00:14) Simplifying Calculations
(00:24) Using VBA to Hide Rows
(01:08) Slow Solution
(01:30) Alternative Solution using AutoFilter
(02:01) Changing Formula to Improve Speed
(02:31) Recording and Using VBA Code
(03:08) Faster Solution using SpecialCells
(04:00) Benefits of Using SpecialCells
(04:29) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass 

This video answers these common search terms:
AutoFilter function
Converting text to numbers
Editing formulas
Efficient data manipulation in Excel
Hide and unhide rows
Hiding rows based on conditions
Looping in VBA
Pivot table analysis
SpecialCells function
Speeding up calculations
Using the macro recorder
VBA coding

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152736/



Welcome back to the MrExcel netcast, where we tackle all your Excel problems and find the most efficient solutions. In this episode, we're going to discuss how to avoid a loop in your Excel calculations. This is a common issue that many Excel users face when dealing with large amounts of data. But don't worry, we've got you covered!

During the Power Analyst Boot Camp, a participant asked a great question about how to optimize their VBA code. They had a worksheet with 750 rows of data and a complex formula in column H that would hide or unhide rows based on certain criteria. However, this calculation was taking a long time to run, even with attempts to speed it up. So, we had to find a better solution.

After some trial and error, we discovered that using the SpecialCells function was the key to speeding up the process. By changing the formula in column H to a number and using the SpecialCells function to only select cells with text, we were able to create a loop that ran much faster. In fact, it went from taking minutes to just one second! This is a game-changer for anyone dealing with large datasets in Excel.

So, why did we choose to go with a number instead of the original formula? Well, by using a number, we were able to use the Go To Special function to only select cells with text, which greatly reduced the number of iterations needed in the loop. This is a clever and efficient way to solve the problem of a slow loop in your Excel calculations.

I hope this tip helps you save time and frustration in your Excel projects. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. Thanks for watching and we'll see you next time on the MrExcel netcast!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


A cool way to streamline a VBA loop using SpecialCells. Episode 1140 shows you how.

More from this channel