Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Hide Worksheet Tabs in Excel

Sometimes we want to hide tab names in Excel workbooks. This is often needed when we developing applications similar to excel dashboards. Because in these applications we create custom buttons and hyperlinks to navigate between tabs. We usually disable the default tab navigation because it can interfere with the functionality of our application. Other reason is the appearance. Because we can create very professional looking navigation method instead of default tab names.

This is a sample navigation method I created using rectangle shapes and a vba macro.

sample dashboard with hidden tabs

This dashboard has several worksheets. But you can’t see the default tab names. So in this post I will explain you how to hide tabs names in an Excel file.

First click on the File menu.

File menu

Then click on Options.

Options

Excel Options dialog box will appear like this. Then select the Advanced category.

Select advance in excel options

Then scroll down to ”Display options for this workbook” section. Remove the tick from “Show sheet tabs” and click OK.

Remove tick from show sheet tabs

Then the default tab names will disappear. Before hide tabs you should create buttons or hyperlinks to navigate between tabs. Also note that this is a file level control. So if you want to remove default tab names for more than one file then you will need to do it for each file separately.

Sum Function (Worksheet)

In this post I’m going to show you how to use Sum function in excel worksheets.

Sum Function character

We use sum function to add values in Excel. Parameters for the sum function can be entered in few different ways. In this post I will show you these different methods one by one. Let’s consider this example sheet.

Sample data

So if we want to find total quantity using sum function, we can do it in few different ways. First method is we can directly enter the values inside sum function like this.

=SUM(9,10,4,6,3,8,1)

enter values directly in Sum function

Then we will get 41 as total.

Result

However if you use this method, total won’t change when you do changes to the quantities of the individual stones. So if you want to use sum function in such a way that total value changes when individual quantity of stones change, then you need to use cell references instead of direct values. Here is how you can do it.

=SUM(B2,B3,B4,B5,B6,B7,B8)

sum function with cell references

Now if you do any change to a individual quantity, it will be reflected in the total instantly. In above method we used individual cell references inside the Sum function. Next let's learn how to use ranges instead of individual cell references. You can simple replace cell references with the relevant range like this.

=SUM(B2:B8)

Sum function with range

In above example we used Sum function for contiguous range. So what if we want to use Sum function for non-contiguous range like this.

Non-contiguous range example data

I have highlighted the non-contiguous range in yellow. So now let’s learn how to use Sum function for this non-contiguous range. If we want to enter values directly then we can use Sum functions like we did earlier. So it will look like this.

=SUM(9,10,4,6,3,8,1,2,4,7,5)

And if we want to enter cell references then we can do it as follows.

=SUM(B2,B3,B4,B5,B6,B7,B8,E2,E3,E4,E5)

cell reference method in Sum function for non-contiguous range

And this is how you can enter range for Sum function for non-contiguous range.

=SUM(B2:B8,E2:E5)

Range input for Sum function for non-contiguous range

How to remove duplicates in excel

In this lesson I will explain you how to remove duplicates in excel. We often deal with excel or csv files which contain duplicate values. Sometimes duplicate values add meaning to the data. But sometimes these duplicates are troublesome. Specially when we analyze data. Now I will explain step by step, how to remove duplicates from your excel or csv files. Let’s consider this sample excel file. As you can see, I have highlighted the duplicates in this excel sheet.

Sample data with duplicate values

To remove duplicates, first click on one cell inside the data range. Then go to the “Data” tab and click on “Remove Duplicates”

Go to Data tab

“Remove Duplicates” dialog box will pop like this.

Remove duplicates dialog box

Select all the checkboxes and click OK. You will get result like this.

result data

Also we can do that using a vba macro as well. Below is the code to do that.

Sub RemoveDuplicates()

ActiveSheet.Range("$A$1:$C$20").RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes

End Sub

In above example, values will be removed only if columns A,B and C all have same data. Next let’s learn how to remove rows if a particular column has same data. Let’s consider this sample data set.

sample data

In this example sheet, I have highlighted the duplicate values we can find in column A. As you can see if we consider both column A and B then data are not same. In this example we are going to remove duplicates only based on column A. To do that first click on one cell within the data set. And then go to “Data” tab like in previous example and click on “Remove Duplicates”. Then remove duplicates dialog box will appear. In this dialog box only put tick to the “First Name” checkbox.

Remove duplicates dialog box

Then click OK and you will get this result.

Result data

Also you can use below macro to do it automatically as well.

Sub RemoveDuplicatesFromColA()

ActiveSheet.Range("$A$1:$B$17").RemoveDuplicates Columns:=1, Header:=xlYes

End Sub

How to Print Automatically (Excel VBA)

In this post I will explain how we can take printouts automatically using vba. So let’s consider this sample sheet.

This is a employee ID of a company. We use this ID as a template to generate IDs for each and every employee of the company. Now let’s look at how we can print this ID. We can do it easily like this.

Sub PrintID()

Dim WS As Worksheet

Set WS = Worksheets("Sheet2")

WS.Range("B2:J13").PrintOut

End Sub

Here "Sheet2" is the name of the worksheet. And Range("B2:J13") is the area we need to print. So you should modify the sheet name and range according to your worksheet name and range, you want to print. If you run above code it will print the ID automatically.

So we learned how to take printout of a range we want. But what if we need several copies of that range. We can take several copies automatically using following code. This below code will print 2 copies of that same range.

Sub PrintID_2Copies()

Dim WS As Worksheet

Set WS = Worksheets("Sheet2")

WS.Range("B2:J13").PrintOut Copies:=2

End Sub

Note that using this above code you can only print one ID. However if you need you can improve this macro to print IDs for all the employees in a database. Let’s assume you have a database with information of your employees. Then you can use For loop and cell addresses to create and print ID for each and every employee in that database.

Contact Form

Name

Email *

Message *

Popular Posts