Excel VBA Tricks

If you are working in Excel and want to make your work easy then today I am providing some VBA tricks and from these few lines of can definitely divide your task in to small chunks. So now without wasting your and my time I am coming to the point.

Here I am discussing Some tricks

•   Find Number of tabs between two tabs using Excel VBA
•   Find Empty Sheet using Excel VBA
•            Find header of sheet using Excel VBA

Find Number of tabs between two tabs using Excel VBA 

I am providing  a simple trick by which you can perform this task easily for this purpose I am using excel index function, As we know that index function provides number of that tabs like 

This is my working sheet as given below and I want to know index value of given sheet then I will write down few lines of code and it will provide me index value of that sheet as shown below

Sheet shows I am working inside tab1 and if I want to know index value of that tab then I will perform this operation

Code:

Sub CountNosheet()  

MsgBox Sheets("tab1").Index

End Sub 

And from pervious Operation I think you will have understood that what will be my next Operation for finding number of sheets between two sheets using VBA.

Yes now I will find index value of that tab in between which I have to find number of sheets and I will perform same Operation finding another index value.

Code : 

Sub CountNosheet()  

MsgBox Sheets("tab5").Index

End Sub 

End sub

Out Put:

 Now, to find number of sheets between these two tabs we follow previous steps combined

Code:

Sub CountNosheet()

Dim firstTab As Integer
Dim lastTab As Integer
Dim noOfTab As Integer

 'index value of FirstTab

firstTab =Sheets("tab1").Index

'index value of lastTab

lastTab = Sheets("tab5").Index 'No of tabs

noOfTab = lastTab - firstTab

MsgBox "No of Sheets between Two tabs :" & noOfTab

End Sub

OutPut :

Find Empty Sheet using Excel VBA

During developing a tool using VBA we have to face a common problem i.e which sheet is required for us and which is not and for this purpose we checked which worksheet contains data and which does not. Hence below trick is used Active sheet is empty or not.

Code:

Sub checkEmptySheet()
    If ActiveSheet.UsedRange.Address = "$A$1" Then

    MsgBox "Sheet is Empty"

    Else

    MsgBox "Sheet is not Empty"

    End If

End Sub

OutPut :

Find header of sheet using Excel VBA

During working inside sheet we have faced many times a single problem that we have to checked header of  given worksheet and for this purpose we have to use loop and as we know that loops always increase our time complexity.

Here is our sheet which contains header

Here is code for finding header

Code:

Sub findHeader()

Dim rang As Range, header

'rang is taken as Range

'header is taken as variant

Set rang = Sheets("tab3").Range("1:1")

header = Application.Transpose(Application.Transpose(rang.Value))

MsgBox Join(header, " ")

End Sub

Out Put:

 Importance of this trick

This trick can be used widely for comparing two sheets because when comparing of two sheets comes in our mind then a common option arises that at first we compare header of one sheet from header of another sheet.

And to full fill this operation we can use above trick in this case we have to select header of two different sheets and comparing those headers.

First sheet :

Second Sheet

Code :

Sub compareHeader()

Dim rang As Range, header

Dim rang1 As Range, header1

'rang is taken as Range

'header is taken as variant

Set rang = Sheets("tab3").Range("1:1")

Set rang1 = Sheets("tab4").Range("1:1")

header = Application.Transpose(Application.Transpose(rang.Value))

header1 = Application.Transpose(Application.Transpose(rang1.Value))

If Join(header, " ") <> Join(header1, " ") Then

MsgBox "Sheets  are Different"

Else

MsgBox "Sheets  are Same"

End If


End Sub

OutPut: