Add-in – Save Excel 2007 file as Pdf

Many times we need to send our Excel files to someone but we don’t want them to be able to edit our files. One of the ways that we can use to do that is to save the Excel sheet as a PDF file. With Office 2007 we don’t need a third party tool to convert our sheets, we can install an Add-in from Microsoft that can be found on the link below:

http://www.microsoft.com/downloads/details.aspx?FamilyId=4D951911-3E7E-4AE6-B059-A2E79ED87041&displaylang=en

This Add-in allows you to export not only Excel sheets but also other Office 2007 programs. The sheets can be exported either to PDF or XPS format. It also allows to send as an email attachment in this formats.

VBA – Combine sheets data into one sheet

I have a Workbook with multiple sheets, all with the same configuration, with headers on row 1 and data starting from row 2.
I want to combine the data from all the sheets into one single sheet called “Target”. I have to go through all the sheets and check what is the last row and the last column with data to define the range that I’m going to copy to the Target sheet. I think that the code is all well commented for you to understand how this is done. This is a simple example how to combine data from multiple sheets into one single sheet. This can be done other ways or can be more complex with sheets with different columns or so. This will be handled in future articles.

Sub CombineSheets() 

'This macro will copy all rows from the first sheet
'(including headers)
'and on the next sheets will copy only the data
'(starting on row 2)

Dim i As Integer
Dim j As Long
Dim SheetCnt As Integer
Dim lstRow1 As Long
Dim lstRow2 As Long
Dim lstCol As Integer
Dim ws1 As Worksheet

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

On Error Resume Next

'Delete the Target Sheet on the document (in case it exists)
Sheets("Target").Delete
'Count the number of sheets on the Workbook
SheetCnt = Worksheets.Count

'Add the Target Sheet
Sheets.Add after:=Worksheets(SheetCnt)
ActiveSheet.Name = "Target"
Set ws1 = Sheets("Target")
lstRow2 = 1
'Define the row where to start copying
'(first sheet will be row 1 to include headers)
j = 1

'Combine the sheets
For i = 1 To SheetCnt
Worksheets(i).Select

'check what is the last column with data
lstCol = ActiveSheet.Cells(1, Activesheet.Columns.Count).End(xlToLeft).Column

'check what is the last row with data
lstRow1 = ActiveSheet.Cells(activesheet.rows.count, "A").End(xlUp).Row

'Define the range to copy
Range("A" & j, Cells(lstRow1, lstCol)).Select

'Copy the data
Selection.Copy
ws1.Range("A" & lstRow2).PasteSpecial
Application.CutCopyMode = False

'Define the new last row on the Target sheet
lstRow2 = ws1.Cells(65536, "A").End(xlUp).Row + 1

'Define the row where to start copying
'(2nd sheet onwards will be row 2 to only get data)
j = 2
Next

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

Sheets("Target").Select
Cells.EntireColumn.AutoFit
Range("A1").Select

End Sub

Excel Charts – Do not show empty dates

When we create a new chart in Excel, if the X axis labels are dates, Excel will assume “Automatic” on the Primary axis Category. Please check image below.

altOn this example I have dates 1, 2, 5, 6 and 7 of October. Only this dates have data on it.
 
altWith the option Category X axis set to Automatic, Excel will produce a chart like Chart 1:

alt

As you can see, Excel has added the dates that where missing between 2 and 5 of October. This dates are not a part of our original table and don’t have value to be charted so I don’t want to display them. This happens because Excel assumes that this is a Time-scale category chart. To remove this dates, go to Chart Options and set the Primary Axis-Category X axis to “Category”. This way we get a chart like Chart 2:

alt

Splitting a string

There are numerous occasions when we need to split a string into pieces. Depending on the type of string that we are handling we can do it using many different techniques. Here I’m going to show you one way of doing a simple string split. On column A we have the strings that we want to split. We want to get the text that is before the “,” character into column B.

Split_String
So in column B we can put the following formula:

=TRIM(LEFT(A1,FIND(",",A1)-1))

Here I use 3 Excel functions: TRIM(), to remove the spaces on the beginning and on the end of the string that we get; LEFT() to get the string to the left of the “splitter”, in this case is the , character and FIND() to find the position of the “splitter” character. I remove 1 from the position that FIND() returns so that I don’t get the “,” character.

In column C we can put the following formula to the the text that is on the right of the “,” character:

=TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))

Here, instead of the LEFT() function, I use the RIGHT() because I want to get the text on the right of the “splitter” character. I use the LEN() function to get the number of character that the string on column A has so that I can subtract the position where the FIND() function finds the “splitter” character. This way I get the number of characters for the function RIGHT() to return, counting from the end of the string in column A. Example: the string in cell A1 has a LEN(A1) of 25 characters. The “splitter” character is found on position 9 of the string. So my formula will return on cell C1 (25-9)=16 characters counting from the right of string in cell A1, the result will be “2nd Street 2009”.

VBA - Convert emails to hyperlinks

On a sheet column I have email addresses and I want to convert them to hyperlinks so that when I click them, I open my email client and send and email to that address. I can use a macro to change the content of that cell to a hyperlink. Here’s the code for that:

Sub Convert_to_Hyperlink()

    Dim rng As Range
    Dim cell As Range

    'Define here the range where you have your emails values
    rng = Range("B2:B100")

    For Each cell In rng
        'If the cell is blank, ignore it
        If cell.Value <> "" Then
            cell.Hyperlinks.Add ANCHOR:=cell, Address:="mailto:" & cell.Value, TextToDisplay:=cell.Value
        End If
    Next cell

End Sub

Create random combinations in Excel

Imagine that you need to create random combinations of letters in Excel. In this example I want to create a 3 letters random combinations list. I want the result to be like this:

TEX
JYY
QCX
CDH
NTW

To get this kind of combinations I used the following formula:

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))

I’ve used the RANDBETWEEN() function to generate values between 65 and 90 because they are the ANSI codes of letters A to Z. The RANDBETWEEM() function has the following arguments:

RANDBETWEEN(bottom,top)

bottom is the smallest integer that the function will return and top is the highest.

After the generation of the random number, I use CHAR() function to return the corresponding character from the ANSI table of characters. This function has the following syntax:

CHAR(number)

number is a number between 1 and 255 that specifies which character we want to return.

On my formula, combining 3 times the formula CHAR(RANDBETWEEN(65,90) I get a combination of 3 letters.

If I wanted to get a random list of numbers between 100 and 1000, I could use the following formula:

=RANDBETWEEN(100,1000)

This will give me a list of number like the one below:

941
486
970
952
376

VBA – Delete rows based on condition

One of the most common questions I get is how to delete rows based on one or more conditions. For instance, you want to delete all rows in your sheets that have the name “John” in column A. Here’s the code to do that:

Sub Delete()

    Dim startrow As Long
    'starting row number here
    startrow = 1
    ' Assuming data to check is in A Column
    Do Until startrow > Cells(Cells.Rows.Count, "A").End(xlUp).Row
        If Cells(startrow, 1).Value = "John" Then
            Rows(startrow).Delete
        Else
            startrow = startrow + 1
        End If
    Loop

End Sub

Highlight duplicates

One of the most common tasks that an Excel user needs to do is to find duplicates on a list of data. There are several ways to do this, depending on the needs. One of the ways is to highlight the values that are duplicated on a column. This can be done using the Format-Conditional Formatting option on the menu bar. Here's an example:

Highlight-Duplicates
On cell A2, I created a Conditional Formatting with a condition "Formula is" and putted the function =COUNTIF(A:A,A2)>1. The COUNTIF function has the following syntax:

COUNTIF(range,criteria)

range is the area where you you want to check if it finds the value specified by criteria.

This will return TRUE if the value from cell A2 is found more than 1 time in column A. If this is TRUE it will apply the Format that I defined. I then Copy and Paste Special-Format to the rest of the cells in column A.

In Excel 2007 and 2010, you can use the a new option on the Conditional Formatting menu that is the Duplicate Values.

Highlight-Duplicates2
Just select the range where you want to highlight the duplicate and go to the menu bar and choose Highlight Cells Rules-Duplicate Values. A Duplicate Values dialog box will open. You can choose to highlight the Duplicate or the Unique values and you can set the color that you want to use to highlight the values.
Highlight-Duplicates3
The result will be the same as using the COUNTIF solution. On Excel 2003 you don’t have this Duplicate Values option on the Conditional Formatting menu.

IF() – Checking conditions

The IF() function is one of the most used in Excel. It’s very simple to use. The syntax for this function is:

IF(logical_test,value_if_true,value_if_false)

logical_test is the value or expression that we want to evaluate. value_if_true is the result if the condition of logical_test is true and value_if_false is the result if the condition is false.

Examples:

=IF(A2=100,"True","False") will check if there is a value 100 in cell A2. If true, will put on the cell where we have the formula, the value “True”. If the value in cell A2 it’s not 100 the formula will return “False” on the cell.

=IF(AND(A2=100,A3=200),"True","False") this is the same as the last example with the exception that in this case we are checking two condition and only if both are true, the result will be “True”.

VBA – Copy all Worksheets to another Workbook

If you want to copy all of your worksheets from the actual workbook to another workbook, you can use this simple VBA code to do it.

Sub copy_sheets()

    Dim wb1 As Workbook
    Dim wb2 As Workbook
    wb1 = ActiveWorkbook
    'Change the name of the destination workbook here
    wb2 = Workbooks("Destination.xls")
    For Each Sheet In wb1.Sheets
        If Sheet.Visible = True Then
            'copy the sheets after the last
            'sheet of the destination workbook
            Sheet.Copy After:=wb2.Sheets(wb2.Sheets.Count)
        End If
    Next Sheet

End Sub

DATEVALUE() - Convert text values to dates

Sometimes we have values on our sheets that are stored as text (strings) and we need to convert them to date format. For this we can use the DATEVALUE() function. The syntax for this function is:

DATEVALUE(date_text)

date_text argument is a text string.

This function can convert various types of strings. Here are some samples:

DATEVALUE("15MAR") will return 15-03-2009
DATEVALUE("MAR09") will return 01-03-2009
DATEVALUE("15/3") will return 15-03-2009
DATEVALUE("1-MAR") will return 01-03-2009

Replace value on several Excel files

I’ve written this code for a user that wanted to change a value in hundreds of Excel files that where in several sub folders under a main folder. This code will go through the main folder C:\Test and will replace the value on cell A1 on Sheet1 of every Excel file it will find. You can adapt this code by changing the folder name, cell destination or sheet name.

Sub Change_Value_On_Files()

    Dim fso, folder, files, NewsFile, sFolderSet
    objExcel = CreateObject("Excel.Application")
    objExcel.Visible = TrueSet
    fso = CreateObject("Scripting.FileSystemObject")
    sFolder = "C:\Test"
    Set folder = fso.GetFolder(sFolder)
    Set files = folder.files
    For Each SubFolder In folder.SubFolders
        For Each folderIdx In SubFolder.files
            If Right(folderIdx, 4) = ".xls" Then
                Set objWorkbook = objExcel.Workbooks.Open(folderIdx)
                Set objWorksheet = objWorkbook.Worksheets(1)
                Set objRange = objWorksheet.Range("A1")
                objRange.Range("A1").Value = "YOUR VALUE HERE!"
                objWorkbook.Save
                objWorkbook.Close True
            End If
        Next
    Next

End Sub

VLOOKUP() - Get value based on another value

If we have a table of data, like the one shown bellow, and based on a value of that table want to return the value from another column we can use the VLOOKUP() function.

VLOOKUP 
The syntax for this function is:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

In our example, on cell B10 I've putted this formula:

=VLOOKUP(A10,$A$2:$B$7,2,FALSE)

First we have the lookup value that is the value that we want to check is it's on the table. In this example is the value on cell A10.

Then we have the table array. This is the range of cells where we want to check for the value on cell A10. In this case, we want to lookup the value on range A2:B7. If it finds the value of A10, it will the return the value referred by the col_index_num argument of the function. In this case we want to return the value from column 2 (the employee name).

The range_lookup argument is optional and it can have TRUE or FALSE value. If you put TRUE or omit this argument, it will return an approximated match. If it doesn't find the value of the lookup_value it will return the next largest value that is less than the lookup_value. If you put FALSE, it will find an exact match and if it doesn't find one it will return the error value #N/A.

So, on this example, we want to find the value 32893 and get the corresponding value from column 2, in this case the returned value will be James Watson.

If the value specified on the lookup_value is not found, and the range_lookup is FALSE, then the formula will return a #N/A error value. We can change our formula so that it doesn't display this error with this formula:

=IF(ISNA(VLOOKUP(A10,$A$2:$B$7,2,FALSE)),"",VLOOKUP(A10,$A$2:$B$7,2,FALSE))

In this case we use two more functions, the IF and ISNA. I will not explain the IF function in this article, will handle this function on a separated article on the future.

The ISNA() function checks if the VLOOKUP() function returns a #N/A error value. So this formula will check if the VLOOKUP() function returns #N/A. If it does, the cell will be empty. If it doesn't it will show the result of the VLOOKUP function.

SUMPRODUCT() - Sum values based on conditions

Sometimes we want to sum values from a table based on conditions that we input into another cell. Take a look at the table below. Here we have a table of weekly values for each vendor.

SUMPRODUCT
In this case we want to input on cell B7 the week value and get the total for this week on cell C7. This can be done using the SUMPRODUCT() function. The syntax for this function is like this:

SUMPRODUCT(array1,array2,…) you can have up to 30 arrays.

The formula in C7 for this will:

=SUMPRODUCT((B1:E1=B7)*(B2:E4))

Checks on range B1:E1 for the value in cell B7 and sums the values under that value. On this example the value "Week 2" can be found on C1 so the sum will be done with the values from C2:C4 and the result will be 338.

Another example is putting in B9 the vendor name and get the total for this vendor on cell C9. The formula will be like this:

=SUMPRODUCT((A2:A4=B9)*(B2:E4))

Checks on range A2:A4 for the value in cell B9 and sums the values for that row where it finds the vendor. On this example the vendor "Vendor 3" can be found on row 4 so the sum will be done with the range B4:E4 and the result will be 492.

Finally we want to get the value for Week 2 for Vendor 3. We can use also the SUMPRODUCT function like this:

=SUMPRODUCT((B1:E1=B7)*(A2:A4=B9)*(B2:E4))

It will return the value 114 that corresponds to Vendor 3 (row 4) and Week 2 (column C).

These are simple examples of how you can use the SUMPRODUCT function. It can be used in more complex formulas that I will address later on another article.

About me

I live in Setúbal, Portugal. I work with Excel for more than 10 years. This tool is so powerful and complete that I'm still learning to work with it. Everyday I discover new fixtures and ways of doing things in Excel.
I'm an active member of the Experts Exchange Community forums. where I’ve more than 1,600 questions answered. I try to help others solve their problems in Excel and also in other tools, mainly Microsoft tools (Access, Outlook, Visual Basic .Net, Powerpoint).
I'm also a Microsoft Office Specialist. I'm a self-taught person but I'm also taking a degree in Information Systems Management.
I started writing this blog to share with others what I've learned over the years that I use Excel and with the experience in problem solving that I've got from my participation on Excel forums. Hope that you recognize the work I've done here.
 
Several articles published on other websites:
Microsoft Excel Blog:
 
Pplware (Tech page from SAPO Portuguese portal – articles in PT):
 
VLOOKUP Week:
 
Programar Magazine (article in PT):
 
Several tips & tricks videos published on Microsoft knowledge sharing site:
120segundos.net (Videos in PT): http://120segundos.net/videos/pesquisa?q=excel

Contacts

You can contact me by email at: jpgpinto (at) gmail (dot) com

Would like to hear from you. If you have some comments or sugestions about this blog, feel free to send me an email. I'll try to answer as soon as possible.

Consulting

I'm available to do consultant work. Depending on your project, I can take a look and provide a custom quote for it. Each project is a challenge, so I don't work with a fixed fee. I can handle, for instance:

- Worksheets/ workbooks improvement;
- Custom applications development;
- User forms development;
- Excel Integration with other Office programs (Outlook, Powerpoint, etc)
- VBA features to automate your work;
- Worksheet problem solving;
- Excel Training.

You can contact me by email at: jpgpinto (at) gmail (dot) com. Drop me an email and I will find the solution for your problem.