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.
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.
Data can be kept in different media, Sometimes, the data need to be extracted, transformed or loaded in different ways.
For this article, I'm going to demonstrate an effective way to find the last line content of a large text file using VBA codes.