Personal Macro in Excel
VBA

Quick Data Formatting by Personal Macro

In this article you will learn how to create a Personal Macro in MS Excel. I have created this personal macro to format the data quickly.Any macros that is stored in your personal workbook become available whenever new excel opened on that same computer for the same user.

To create a personal macro first of all we have enable the Personal.xlsb in Visual Basic Editor. Below are the steps to enable the Personal.xlsb in Visual Basic Editor-

  • Go to the View Tab>>Macros>>Record Macro..
Record Macro
Record Macro
  • Record Macro window will be opened.
  • Select “Personal Macro Workbook” in Store macro in drop-down.
Record Macro window
Record Macro window
  • Go to the View Tab>>Macros>>Stop Recording.
Stop Recording
Stop Recording
Personal.xlsb
Personal.xlsb
  • Macro1 is available in Module1
Module 1
Module 1
  • Delete the entire code available in Module1 and copy below given code.
Option Explicit
Sub Format_Data()

Dim rng As Range
Dim Header_confirmation As Integer
Header_confirmation = MsgBox("Is first row headers?", vbYesNo + vbQuestion)

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.LineStyle = xlHairline
.Borders.Weight = xlThin
.Borders.ColorIndex = 15
.EntireRow.RowHeight = 15
.EntireColumn.ColumnWidth = 15
.Font.Name = "Calibri"
.Font.Size = 10
.Font.Italic = False
.Font.Bold = False
.Font.Underline = False
.Interior.Color = xlNone
.Font.ColorIndex = 1
End With

ActiveWindow.DisplayGridlines = False

If Header_confirmation = vbNo Then Exit Sub

Dim cel As Range
Dim c1, c2 As Integer
Dim r As Long

For Each cel In Selection
c1 = cel.Column
r = cel.Row
Exit For
Next

c2 = Selection.SpecialCells(xlCellTypeLastCell).Column

With Range(Cells(r, c1), Cells(r, c2))
.Font.Bold = True
.Interior.ColorIndex = 23
.Font.ColorIndex = 2
.Font.Size = 11
End With

End Sub
  • After pasting the above code in Module1, click on go to File menu in VBE and click on Save PERSONAL.XLSB  or press Ctrl+S in Module1.
Module 1 after pasting the code
Module 1 after pasting the code

Now we will give shortcut key to run this macro.

  • Go to the excel worksheet and press Alt+F8.
  • Macro window will be opened.
Macro window
Macro window
  • Click on Options button.
  • Macro Options window will be opened.
  • Put shortcut key as Ctrl+m.
Macro Options
Macro Options
  • Go to visual basic editor(Press Alt+F11).
  • Go to file menu and save PERSONAL.XLSB

Let’s say we have below given data which is to be formatted.

Data which is to be formatted
Data which is to be formatted
  • Select the above given data and press Ctrl+M.
  • Below given confirmation message box will be opened. Click on Yes button.
Confirmation Message box
Confirmation Message box
  • Data will be formatted like below given image.
Formatted Data
Formatted Data

Let’s take another example to format below given data-

Data to be formatted without headers
Data to be formatted without headers
  • Select the data and press Ctrl+M.
  • Click on No on confirmation message.

Data will be formatted without headers as given in below image.

Formatted data without headers
Formatted data without headers

If the data is available in middle of the worksheet and it has some cell background color, bold font, different font size etc.

 

Data to be formated
Data to be formated
  • Select the data and press Ctrl+M.
  • Click on Yes in header confirmation message.
Data has been formatted.
Data has been formatted.

Click here to download the practice file

Watch the video tutorial how to create a personal macro

 

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 12 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com

Leave a Reply