Basic knowledge of Excel VBA, 10 tips You should learn.

Now we will start the coding part. First, we create a module. To insert module, Goto Developer Tab –> Visual Basic –> then follow as below picture.

.

.

.

.

Define worksheet in excel vba

Dim sh as worksheet
set sh = sheets("sheet_name")

How to find last row number with non-blank cell

Dim i As Long
 i = sht.Range("D" & Rows.Count).End(xlUp).Row

It will find Last Row number by i. If you want to insert data into last blank cell then use +1. you should follow below example.

Dim i As Long
 i = sht.Range("D" & Rows.Count).End(xlUp).Row + 1

How to protect and unprotect worksheet by vba

Dim sh as worksheet
set sh = sheets("sheet_name")
sh.protect "password" ' for protect the sheet
sh.unprotect "password" 

To print Date and time in excel cell through vba

sh.Range("G7").Value = Date 
Range("M20").Value = Now

Cells validation by vba code. For example I choose G5 cell

If sh.Range("G5").Value = "" Then ' for Check Blank Cell
   MsgBox "Please Select Head of Account" ' for msg box
   sh.Protect "121"
  Exit Sub
End If

Validate if your worksheet is on filterMode, then create an alert or message box to clear the filter. Here I used sht as worksheet. You will see the full vba code in next page

If sh.FilterMode = True Then
MsgBox "Pease Check and clear filter from the Transaction"
Exit Sub
Else
End If