Simple Macro In Excel And Automate The Repetitive Process

Background

In today's world, whatever may be the company size or field, Excel becomes the most useful/unavoidable Application. In this blog, I am going to share one of my real time experience, which saved huge amount of time in a work done in Excel. Hope it will help to save your time too.

Few days ago, there was a need in our project to insert the huge (around 30,000) records of data to the database from Excel. There was a problem in Excel that the data was not in the correct format. Thus, I used macro to correct the format.

For Example


(Before running macro, original/unformatted sample data from Excel is given above)

In the above snapshot, given above, just see the row no 3 & 4 in it. For us to get the correct format, the description has to merge as a one value, Unit and Rate value has to move to the previous row (i.e.) 3rd row and finally the entire 4th row has to deleted, since it will became empty .

Following the same criteria, we had around 5000 rows, so just think! If we do this manually, how much time and effort will it take? To avoid it, I used a macro and completed the process within few seconds.

Output

After running the macro, sample formatted data is given below.



Now I am going to show the process, which I did to get the output, shown above. As a first step, we need to enable the developer tab in Excel. By default, it won’t be available. To enable it, just see the snapshot, given below especially Office button ->Excel Option->Popular.



Check the box and you can see the new tab will be added to Excel like, as shown below.



Next step is to click Visual Basic icon and you will get the screen, given below.



Now right click on Microsoft Excel object folder and insert the module, as shown below.



This is the place, where we need to write our logic or requirement.



The code with the description is given below for each line.

Sub moveunitandrate() 'method name,we again assign any name
Dim targetedrow, Current_Row, Start_Row, End_row As Integer 'declaration of variables

Start_Row = 2 'Starting row of Excel
End_row = 17 'Upto which row we need to process

Worksheets(1).Activate 'Before get/set the value in excel , we need to activate the worksheet which we need to work

For targetedrow = Start_Row To End_row 'loop to process start row to the end row of excel

'Below 1 refers A column, (for ex) first time when loop execute it will check if [2nd-row A-column] value is empty or not If (IsEmpty(ActiveSheet.Cells(targetedrow, 1).Value)) Then

'Below 2 refers B column,this logic just merging the description."

ActiveSheet.Cells(targetedrow - 1, 2).Value = ActiveSheet.Cells(targetedrow - 1, 2).Value + " " + ActiveSheet.Cells(targetedrow, 2).Value

'Below 3 refers c column, this logic moves the unit
ActiveSheet.Cells(targetedrow - 1, 3).Value = ActiveSheet.Cells(targetedrow, 3).Value

'Below 4 refers d column,this logic moves the unit

ActiveSheet.Cells(targetedrow - 1, 4).Value = ActiveSheet.Cells(targetedrow, 4).Value
'finally this logic deletes the entire row
ActiveSheet.Rows(targetedrow).EntireRow.Delete

End If 'End of if condition

Next targetedrow 'End of for loop

End Sub 'End of method

Reference

• MSDN-Excel VBA Reference
• MSDN-Refer to Sheets by Index Number

You can also see this or other related articles in my website.

Conclusion

I have explained the code by providing the comments for each line, so that it will be easy understandable . Hope it will be surely helpful. Kindly share your thoughts or feedback.