A quick word about Visual Basic (VB)
Visual Basic is a foundation of automation in Microsoft office. When you create a macro using action, i.e. click, arrow around a spreadsheet, select menu items, etc. in the background Microsoft is capturing these commands and creating a Visual Basic or VB script. If you have the time and energy, it is a relatively simple language to learn, and quite practical for intermediate to advanced Excel users. What makes it better, is the ability to find short and very useful scripts on the internet to automate, or just make life simpler.
Below I am providing a listing of some basic scripts I have found or written that provide some simple, yet useful functions not readily available in Excel. Some of these just save time and effort.
Combining spreadsheets.
Let’s say you have a workbook with 2 or more sheets that you only want to combine. This script gives you the ability to create a worksheet labeled “Combined Data,” and it copies all data from the remaining sheets into it. Keep in mind that after all the data is moved, if you have the same header row or repeating information in each sheet, you may want to consider deleting this. Also, note I said it just copies the data. You will still need to delete the information from the worksheets if the data is no longer required.
Code:
Sub CombineSheets()
‘Works for Excel 2000-2016
‘This example copies all data into a sheet named “Combined Data”
‘Code Provided by Jeff Chamberlain KTL Solutions
Dim sCount As Integer
Dim oSheet As Worksheet
For Each oSheet In ActiveWorkbook.Worksheets
If oSheet.Name = “Combined Data” Then
Application.DisplayAlerts = False
oSheet.Delete
Application.DisplayAlerts = True
Exit For
End If
Next oSheet
ActiveWorkbook.Worksheets.Add Sheets(1)
ActiveSheet.Name = “Combined Data”
For sCount = 2 To Sheets.Count
If Sheets(sCount).Range(“A1”).CurrentRegion.Rows.Count > 1 Then
Sheets(sCount).Range(“A1″).CurrentRegion.Copy
ActiveCell.Value = Sheets(sCount).Name & ” Data”
ActiveCell.Offset(1).Select
ActiveSheet.Paste
ActiveCell.Offset(ActiveCell.CurrentRegion.Rows.Count + 2).Select
End If
Next sCount
End Sub
Emailing workbooks
Emailing workbooks has been a big time saver for me over the years. I have added so much on to this basic script that it now has a few dialog boxes asking for email addresses and subject lines, and other functions. This is the basic stripped down version. The email address, subject and even text of the body are embedded in the script itself. I have tested this out in various versions of Excel, and it works well all the way back to 2000.
Code:
Sub Mail_workbook_Outlook_1()
‘Works for Excel 2000-2016
‘This example sends the last saved version of the Active workbook
‘Code Provided by Jeff Chamberlain KTL Solutions
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject(“Outlook.Application”)
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = “email@email.com”
.CC = “”
.BCC = “”
.Subject = “ADD CUSTOM SUBJECT BETWEEN THE QUOTES”
.Body = “ADD CUTOM TEXT TO APPEAR IN THE BODY OF THE EMAIL HERE”
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Deleting Empty Rows
One of the results of filtering, sorting, and removing rows of data is that you are often left with blank rows in the middle of your spreadsheet which can affect the way it operates on many levels. The subroutine below is a fast and efficient way to delete all these empty rows in the active worksheet.
Sub DeleteEmptyRows()
‘Works for Excel 2000-2016
‘This example deletes all empty rows from the bottom most to the top
‘Code Provided by Jeff Chamberlain KTL Solutions
LastRow = ActiveSheet.UsedRange.Row – 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub
This routine is written so that the loop uses a negative step value, causing the rows to be deleted from the bottom to the top. When working in spreadsheets, especially for editing, I prefer to use this method as it creates a definite starting and ending point. If you have ever recorded a macro, you will know that often, unless you direct the cell reference, it will use the current cell location.
VB script can be very handy. Whether or not you learn to write the code, at the bare minimum, you should understand how it works and how to build it into your spreadsheets for automation. From there, you can find many resources that will either give you some helpful scripts or give you snippets to use in your own.
[avatar user=”jchamberlain” size=”thumbnail” align=”left” /]JEFF L. CHAMBERLAIN, PMP | Project Manager
Jeff comes to KTL Solutions with an extensive background in healthcare IT, technical consulting, and telecommunications. He has been a project manager for almost 20 years, holding certifications from the Project Management Institute as a Project Management Professional, from the Management and Strategy Institute as a Six Sigma Lean Professional, and he holds a Scrum Master Certification from the Scrum Alliance. He has managed both hardware and software implementations for both the government and private sectors, in industries such as healthcare, insurance, telecommunications, staff augmentation, supply chain and shipping.
Jeff has provided training for clients globally, working in Europe, Russia, North and South America on various topics from system optimization to wireless theory and design. He possesses a Bachelor’s Degree in Technical Writing from the University of Baltimore.
