[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


Fix Code to fill Missing data in Excel. Loop through spreadsheet in VBA.

Posted on
10,677 Points
Last Modified:
Experience Level: Beginner
crystal (strive4peace) - Microsoft MVP, Access
Love empowering people by teaching and helping them develop applications.
This Experts Exchange lesson shows how to use VBA to loop through rows in Excel.  In order to sort, filter, and use database features, there needs to be a value in each column for every row. When data arrives with values missing, code to copy values where it is blank can be run.

This lesson shows the manual process to fill blanks, and the VBA code to implement it.

This is Part 2 in a 3-part series to discuss error handling in Excel.

Part 3 of this series will add errors to the code and then show how to fix them.  Part 1 of this Experts Exchange series suggested basic error handling code.

Video Steps

01. Set up the error handler

   At the top of the code for your procedure, the error handler is set up using     On Error GoTo Proc_Err

02. Dimension Variables

   Declare variable names and data types that will be used in this procedure.

03. Initialize Variables

   Initialize the values of variables that will be used in this procedure.

04. Calculate Variables

   Calculate the values of variables that be determined.

05. Give User a Chance to Back Out

   Issue a message box to the user and allow them to stop the process.

06. Determine the Last Row

   Determine the last row on the worksheet that needs to be written to.

07. Loop through each Row of Data and Save or Write Value

   Loop through each row and either save the value that is there, or write the last value saved if a value is needed.

08. Continue Looping until Done

   Continue looping until all values in the specified column are written.

09. Exit Code

   After the procedure code, a line label for the exit code (Proc_Exit: ) is used to signify what happens at the end of the procedure. This is code to gracefully exit.

10. Error Handling Code

   After the exit code, a line label for the error handling code (Proc_Err: ) is used to issue a message box to the user if there is an error, and then exit the procedure if the code is not broken.
1 Comment
LVL 24
This is Part 2 in a 3-part series to discuss error handling in Excel.  It can be watched independently of the other videos.  This presentation focuses on automating a manual process to copy values from one row to the next in a column of an Excel spreadsheet.

You will learn how to loop through rows using VBA.  This example copies values to blank cells, but you could do something else in your loop.

Part 3 of this series will add errors to the code and then show how to fix them.  

Part 1 of this series reviewed basic error handling code.

Attached is the download file for this lesson, and the next: Books_ErrorHandling_02_Code2_CopyDownBlanks.xlsm

Featured Post

Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

When converting amounts between different currencies, you need exchange rates. This article demonstrates how to obtain these from no less than nine different sources, either for free or - for intense or demanding use - for a fee.
If you need to migrate about 500 users in total, manually creating users profiles and copying over settings will be impractical. So read this guide to migrate Outlook profiles to Office 365 with a reliable tool.
Total Time: 17:40

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month