# Excel VBA Exercises: 10 Real World Problems

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.

## 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.

Get FREE Advanced Excel Exercises with Solutions!

Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF