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

Posted on
10,663 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

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Join & Write a Comment

Are you among those users who need to move email from Outlook to Office 365 or Exchange Online? Are you facing an issue in importing Outlook PST files or does not have an idea how to migrate PST file to Office 365? Then you will get an exact solutio…
This post is just to show case how to provide Guest access for Microsoft Teams, As the Office 365 admin, you must enable the guest feature before you or your organization's users (specifically, team owners) can add guests.
Total Time: 17:40

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month