Basic codes - VBA

Add Serial Number

This macro code will help us to automatically add serial

numbers in our Excel sheet which can be help us if you work with large data.

Sub AddSerialNumbers() Dim i As Integer On Error GoTo Last i = InputBox("Enter Value", "Enter Serial Numbers") For i = 1 To i ActiveCell.Value = i ActiveCell.Offset(1, 0).Activate Next i End Sub

Insert Multiple Columns

This code helps you to enter multiple columns in a single

click. When you run this code it asks you the number columns you want to add and when you click OK, it adds entered number of columns after the selected

cell.

Sub InsertMultipleColumns() Dim i As Integer Dim j As Integer ActiveCell.EntireColumn.Select On Error GoTo Last i = InputBox("Enter number of columns to insert", "Insert Columns") For j = 1 To i Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightorAbove Next j Last: Exit Sub End Sub

Insert Multiple Rows

With this code, you can enter multiple rows in the

worksheet. When you run this code, you can enter the number of rows to insert and make sure to select the cell from where you want to insert the new rows.

Sub InsertMultipleRows() Dim i As Integer Dim j As Integer ActiveCell.EntireRow.Select On Error GoTo Last i = InputBox("Enter number of columns to insert", "Insert Columns") For j = 1 To i Selection.Insert Shift:=xlToDown,  CopyOrigin:=xlFormatFromRightorAbove Next j Last: Exit Sub End Sub

Auto Fit Columns

This code quickly auto fits all the columns in your worksheet. So when you run this code, it will select all the cells in your worksheet and instantly auto-fit all the columns.

Sub AutoFitColumns() Cells.Select Cells.EntireColumn.AutoFit End Sub

Auto Fit Rows

You can use this code to auto-fit all the rows in a worksheet. When you run this code it will select all the cells in your worksheet and instantly auto-fit all the row.

Sub AutoFitRows() Cells.Select Cells.EntireRow.AutoFit End Sub

Remove Text Wrap

This code will help you to remove text wrap from the entire worksheet with a single click. It will first

select all the columns and then remove text wrap and auto fit all the rows and columns.

Sub AutoFitRows() Cells.Select Cells.EntireRow.AutoFit End Sub

Unmerge Cells

This code simply uses the unmerge options which you have on the HOME‌ tab. The benefit of using this code is you can add it to the QAT and unmerge all the cell in the selection.

Sub UnmergeCells() Selection.UnMerge End Sub

Open Calculator

In Windows, there is a specific calculator and by using this macro code you can open that calculator directly from Excel.

Sub OpenCalculator() Application.ActivateMicrosoftApp Index:=0 End Sub

Highlight Duplicates from Selection

This macro will check each cell of your selection and highlight the duplicate values.

Sub HighlightDuplicateValues() Dim myRange As Range Dim myCell As Range Set myRange = Selection For Each myCell In myRange If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then myCell.Interior.ColorIndex = 36 End If Next myCell End Sub

Highlight the Active Row and Column

Here are the quick steps to apply this code.

1. Open VBE (ALT + F11).
2. Go to Project Explorer (Ctrl + R, If hidden).
3. Select your workbook & double click on the name of a particular worksheet in which you want to activate the macro.
4. Paste the code into it and
5. select the “BeforeDoubleClick” from event drop down menu.
6. Close VBE and you are done.

Remember that, by applying this macro you will not able to edit the cell by double click.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim strRange As String strRange = Target.Cells.Address & "," _ Target.Cells.EntireColumn.Address & "," _ & Target.Cells.EntireRow.Address Range(strRange).Select End Sub