Saturday, March 21, 2009

Write Macros in Excel / Clean or Tweak Excel Macro

I am going to cover how to Write excel macro / Clean or tweak recorded excel macro. Those who are not aware about recording excel macro , I suggest them to go through my earlier post on

1. Recording Excel Macro



When you record a macro it records many actions which are actually not required. Pls go through the macro code given below recorded by record macro function of excel.


Sub MergeCells()
Range("B4:C4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
End Sub


This code is doing three actions

1.Select dells in range B4:C4
2.Merge selected cells i.e. B4:C4
3.Unmerge selected cells i.e. B4:C4

If you see there is lots of unwanted action recorded by macro recorder. Similar action can be achieved by cleaning up the unwanted stuff. See the code given below which performs the same action.


Sub MergeCells_clean()
Range("B4:C4").Select
Selection.Merge
Selection.UnMerge
End Sub


Once you have recorded your macro , you can lookout for unwanted stuff recorded by it. Cleaning up that will help you understand your macro better and you will be able to maintain your macro for long time.

Similar action can be achieved by two line code as under :-


Sub MergeCells_simple()
Range("B4:C4").Merge
Range("B4:C4").UnMerge
End Sub

2 comments:

  1. Well, Excel macros are good, but sometimes it isn't enough. In such cases it's better to use some professional tool, like this https://www.jitbit.com/macro-recorder/ for example.

    ReplyDelete