10 Code Macro VBA cơ bản

1. Add Serial Numbers-thêm seria mumber

This macro code will help you to automatically add serial numbers in your Excel sheet.

Once you run this macro it will show you an input box where you need to enter max number for the serial numbers and after that, it will insert numbers in the column in a sequence.

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

Last:Exit Sub

End Sub

2. Insert Multiple Columns-Chèn cột

Once you run this macro it will show an input box and you need to enter the number of columns you want to insert.

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

3. Insert Multiple Rows-chèn dòng

Once you run this macro it will show an input box and you need to enter the number of rows you want to insert.

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

4. Auto Fit Columns-Tự chỉnh kích thước cột 

Quickly auto fit all the columns in your worksheet.

This macro code will select all the cells in your worksheet and instantly auto-fit all the columns.

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

5. Auto Fit Rows-tự chỉnh kích thước dòng

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

6. Remove Text Wrap-bỏ xuống dòng

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 RemoveWrapText()
Cells.Select
Selection.WrapText = False
Cells.EntireRow.AutoFit
Cells.EntireColumn.AutoFit
End Sub

7. Unmerge Cells-bỏ trộn dòng

Select your cells and run this code and it will un-merge all the cells from the selection with your loosing data.

Sub UnmergeCells()
Selection.UnMerge
End Sub

8. Open Calculator-mở tính toán

In window there is a specific calculator and by using this macro code you can open that calculator directly from Excel use for your calculations.

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

9. Add Header/Footer Date-thêm tiêu đề ngày

Use this code to add a date into the header or footer in your worksheet.

You can edit this code for switching from header to footer.

Sub dateInHeader()
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&D"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With
ActiveWindow.View = xlNormalView
End Sub

10. Custom Header/Footer-thêm tiêu đề

If you want to insert a custom header then this code is for you.

Run this code, enter custom value in the input box. To change the alignment of header or footer you can edit the code.

Sub customHeader()
Dim myText As Stringmy
Text = InputBox("Enter your text here", "Enter Text")
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = myText
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With
End Sub