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.

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.

Then click on Options.

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

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

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.

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.

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)

Then we will get 41 as total.

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)

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)

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.

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)

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

=SUM(B2:B8,E2:E5)

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.

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

“Remove Duplicates” dialog box will pop like this.

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

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.

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.

Then click OK and you will get this result.

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.

How to create a line chart automatically - Excel VBA

Charts are another very important elements available in Excel. They play key role in data visualization. Charts have been important in comparing data and analytics. We often use them in reports and dashboards. Because they help to get data-driven insights for the users.Today I am going to show you how to create a chart automatically using VBA. Let’s consider this sample data.

This is a monthly profit of a shop. Now let’s create a chart to compare profit vs months. So months will be in x axis and profit will be in y axis. We can create the chart automatically using below code.

Sub CreateChart_Ex1() Dim WS As Worksheet Set WS = Worksheets("Sheet1") WS.Activate WS.Range("A1:B13").Select WS.Shapes.AddChart2(227, xlLine).Select End Sub

Note that you need to activate the sheet before select the range if you are in different sheet. WS.Activate doesn’t throw error even if you are in that same sheet. You will get this chart if you run above code.

However using this code, you can create a chart only in the worksheet where your data is in. Suppose you have data in one sheet and you need to create chart in another sheet. We have a solution for that too. Assume we have data in Sheet1 and we want to create chart in Sheet3. We can do it like this.

Sub CreateChart_Ex2() Dim WS As Worksheet Dim WS_New As Worksheet Set WS = Worksheets("Sheet1") Set WS_New = Worksheets("Sheet3") WS_New.Activate WS_New.Shapes.AddChart2(227, xlLine).Select ActiveChart.SetSourceData Source:=Range(WS.Name & "!$A$1:$B$13") End Sub

In above examples we created graphs for fixed range. How about if we require to create a chart for dynamic range. Then we have to find the last row of the data, assign it to a variable and then use that variable like this.

WS.Range("A1:B" & LastRow).Select

Also it is a good practice to define the variable at the beginning. You can define the LastRow as integer or long depending on the amount of rows you will have.

This post explains how to find the row number of the last non empty cell of a worksheet.

Best way to get the last row of any column of the excel sheet

So from this post we learnt how to create a chart with one series automatically. If you want to know how to create a chart with multiple series then check out this post.

Create line chart with multiple series using Excel VBA

Convert an Excel Sheet to PDF Using VBA

In my earlier post I explained how to save an Excel sheet as PDF manually. If you want to know how to do it, then check this post.

Save an Excel Sheet as PDF Manually

Today I’m going to teach you how to convert an Excel sheet to PDF automatically. We need this type of functionality, when we develop applications which output reports. Because lot of users need their reports as PDF files. Here is one such report generated by an Excel application.

Now let’s learn how to convert this to a PDF file. First we need to define our variables.

Dim WS As Worksheet Dim FileName As String Dim FilePath As String

I’m going to convert the activesheet to PDF. So I will assign activesheet to WS variable.

Set WS = ActiveSheet

Next we need to give the name to the PDF file

FileName = "Result PDF"

Also we should select the orientation. You should select portrait or landscape according to the length and width of your excel sheet. I will choose portrait for this example.

WS.PageSetup.Orientation = xlPortrait

If you need landscape then you should replace xlPortrait with xlLandscape. Now we have to select the saving location. In this example I will select the same folder where the application is.

FilePath = ThisWorkbook.Path

Or else you can assign specific file path like this.

FilePath = "C:\Users\EVS\Documents\PDF Reports"

We have assign the values to all our variables. And have set the orientation. So now we can convert the sheet as follows.

WS.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FilePath & "\" & FileName & ".pdf", _ Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False

Next we show the confirmation message.

MsgBox "Completed", vbInformation, ""

And here is the complete code to do the job.

Sub SaveAsPDF() Dim WS As Worksheet Dim FileName As String Dim FilePath As String Set WS = ActiveSheet FileName = "Result PDF" WS.PageSetup.Orientation = xlPortrait FilePath = ThisWorkbook.Path WS.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FilePath & "\" & FileName & ".pdf", _ Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False MsgBox "Completed", vbInformation, "" End Sub

When you run above code, result file will be created like this.

Save an Excel Sheet as PDF

Today I’m going to show you how to save an Excel sheet as PDF. Suppose we have an Excel sheet like this.

Now let's learn how to convert this Excel sheet to a PDF file. First click on the “File” menu.

Then click on “Save As”.

Click “Browse”.

It will open Save as dialog box. Choose the folder where you want to save your PDF file.

Select PDF format from the “Select as type” drop-down.

And give suitable name for the PDF file. Then click “Save”.

I have chosen the saving location as the folder, where I have this original excel file. So the PDF file will created like this.

Also if you need, you can convert an Excel sheet to PDF automatically using VBA. Read this post if you want to know how.

Convert an Excel Sheet to PDF Using VBA