Excel - Using Go To Special in an Excel Macro with Special Cells - Episode 1297

Microsoft Excel Tutorial: Using VBA for Go To Special Dialog. Welcome to another episode of the MrExcel netcast. In this episode, we will be discussing VBA and Macros in Excel 2010, specifically the use of GoTo Special. This feature can greatly improve the efficiency of your macros, as we will see in the example we discuss today. In chapter 12 of our VBA book, we cover filters and the use of GoTo Special. This feature came up in a recent seminar I attended, where a participant had a large dataset of 3900 rows. They had a formula in place to determine which rows to hide, but their macro was taking a significant amount of time to run. We will take a look at their original macro and see how we can improve it using GoTo Special. The original macro used a loop to go through each row and check a specific column for a certain value. If the value was true, the row would be hidden. This process was taking 12 seconds to run, and in some cases, even minutes. By utilizing GoTo Special, we were able to significantly reduce the run time of the macro. In fact, the new macro was able to complete the task in just one second, a 12 to 1 improvement. GoTo Special is a powerful tool in Excel, but it is often overlooked by many users. In VBA, the equivalent function is Special Cells, which allows us to specify certain criteria for the cells we want to select. In this case, we were able to use Special Cells to only select the numeric cells, making our macro much more efficient. One thing to note is that the help file for this function suggests using a defined constant, but in our case, using the actual number 1 was more effective. This is a great example of how defined constants can sometimes cause more headaches than they solve. In fact, the Macro Recorder even uses the number 1 instead of the defined constant, showing that even the creators of the program know that the constant is not functioning correctly. In conclusion, GoTo Special is a valuable tool in Excel and VBA that can greatly improve the efficiency of your macros. By using this feature, we were able to significantly reduce the run time of our macro and complete the task in just one second. Thank you for watching this episode of the MrExcel netcast, and be sure to join us next time for more tips and tricks to improve your Excel skills. 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-... Table of Contents: (00:00) Excel VBA GoTo Special (00:14) Using GoTo Special to hide rows (00:25) Writing a simple Macro (00:35) Comparing OldWay and NewWay (00:55) Running the OldWay (01:05) Calculations and repainting the screen (01:15) The OldWay took 12 seconds (01:25) Using GoTo Special in VBA (02:02) The NewWay (02:58) Using defined constants (03:47) Turning on the Macro Recorder (04:03) Running the NewWay (04:20) GoTo Special in VBA (04:41) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Chapter 12 Developer Excel VBA Filters Formulas GoTo Special Hide rows Macro Macro Recorder Special cells Speed up Excel xlNumber Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads... Today, in Episode #1297, Bill looks at the 'Go To Special' Dialog as he shows us how to leverage the Special Cells property to quickly select all rows to hide or delete, using VBA in Microsoft Excel.