Included in this article are ten Excel VBA exercises to test your macro solving abilities. One with advanced knowledge of Excel should be able to solve all problems with ease.
You need to know the following topics to solve the ten problems:
count colored cells, VBA SUMIFS multiple criteria, unhide rows within a range, find the square root, clear the contents of specific cells, save the workbook as a new file, hide rows based on cell value, capitalize the first letter of each word, generate random numbers, and find duplicate rows.
Additionally, you should know how to make VBA code faster and enable the developer tab.
Excel 2019 or a later version is needed to solve the problems. However if you have Excel 2010, you should be able to solve most of the them.
Download Practice Workbook
Problem Overview
There are ten Excel VBA related exercises contained in the file above. The datasets are different for each of the problems. Download and open the Excel file and go through the problem statements. The exercises are provided in the “Problem” sheet, and the solutions are in the “Solution” sheet. In addition, this article’s introduction provides links to the solutions in order.
The first two exercises are shown in the following picture.
Exercise 1 – Count Cells by Fill Color
There are two sets of colors in the dataset. Use VBA to count the number of cells that have the same fill color.
This animated image shows a custom function to count the fill color.
Exercise 2 – Use VBA SUMIFS with Multiple Criteria
The selling price of several products are provided. Add the price of all mobile or AC.
Exercise 3 – Unhide Rows Within Range
We have hidden two rows in the dataset. Prepare a VBA macro to unhide the rows.
Exercise 4 – Find Square Root of Numbers
Eight numbers are given. Calculate the square roots of the numbers and then put the results in the cells next to the original values.
Exercise 5 – Clear Cell Contents If It Contains Specific Value
We have provided two columns of values. Prepare a VBA code to clear cell contents if it contains a specific value (in this case 96) is present.
Exercise 6 – Save This Workbook as a New File
Write a VBA code to save the exercise file as a new file.
Exercise 7 – Hide Rows Based on Cell Value
The dataset contains the grades of six students. Create a VBA code to hide the rows that have grade “F”.
Exercise 8 – Capitalized First Letter of Each Word
Use a VBA code to capitalize the first letter of each word in the two rows of the dataset. Additionally, rather than hard coding the cell range, insert an input box to prompt users to select the cell range.
Exercise 9 – Generate Random Number from Range
Type a VBA code to return random numbers that have a lowest value of 50, and a highest value of 90.
Exercise 10 – Find Duplicate Rows
Highlight the duplicate rows in a range using VBA.
The following image shows the answers to the first two exercises.
