## Excel Used Ghost

Here in the States – All Hallows’ Eve (Halloween) is quickly approaching. One of my favorite times of the year with leaves changing color, daylight getting shorter, a crispness in the air, football season is in full swing, and the Fall Classic (Major League Baseball) begins in a few weeks.

All Hallows’ Eve is a time for hobgobblery and apparitions of all sorts as with Charlie Brown and the Peanuts gang pictured here. Excel too has a ghost. In this post I’ll take a look at one and how we might put it to rest.

### Worksheet UsedRange Property

The Worksheet Object has a UsedRange Property. Normally, we should be able to use this property so that we can quickly identify the entire UsedRange on the Worksheet without having to jump through a lot of hoops.

Here is a Range of Cells with some data. We can quickly find the address of the UsedRange on the Worksheet:

```Option Explicit

Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
Set rng = ws.UsedRange

Set rng = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
```

Output:

\$A\$1:\$J\$10

Great – that is what I expected. But what happens if I delete some data?

### UsedRange…False/Positive

A false positive occurs when we test for data and Excel tells us that there is data when in fact there is not.

Here is the same data as before, but I deleted the data from Columns \$H:\$J. Now I’ll respin the code and check results:

\$A\$1:\$J\$10

Hmmm…same results – that is not good – therefore:

we cannot rely on the UsedRange Property of the Worksheet Object. We need a better way to find the TRUE used range of data

### The Last Used Cell

We can use the Find Method of the Range Object to get the last cell on the Worksheet that contains any data:

```Sub GetLastCell()

Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet

With ws
Set rng = .Cells.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
End With

Set rng = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
```

Output:

\$G\$10

Great – that’s what I was looking for. What happens if there is nothing on the Worksheet? I moved to a new worksheet in the Workbook and tried the code again:

That’s not good. I need to revise my code a bit to handle cases where there is no data on the worksheet:

```Sub GetLastCellHandleNoData()

Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet

With ws
Set rng = .Cells.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
End With

If Not rng Is Nothing Then
Else
Debug.Print "There is no data on worksheet ", ws.Name
End If

Set rng = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
```

I tested the code on a Worksheet with data in \$A\$1:\$G\$10. Results:

\$G\$10

Great – that’s what I expected.

Next I tested on a Worksheet with no data. Results:

There is no data on worksheet Sheet2

Great – that’s what I expected.

I tested the code on a wide variety of scenarios for data placement on the Worksheet. It appears to work for any possible scenario. Please let me know if your tests return unexpected results or errors.

I now have the last used Cell on the Worksheet. Now I need the first used Cell on the Worksheet.

### The First Used Cell

For the first used Cell, I tested Cell(1,1) first and then the remainder of the Worksheet. When data was in Cell \$A\$1, beginning at \$A\$1 and searching was returning the next Cell address.

```Sub GetFirstCell()

Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet

With ws
If Not IsEmpty(ws.Cells(1, 1)) Then
Set rng = ws.Cells(1, 1)
Else
Set rng = .Cells.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End If
End With

If Not rng Is Nothing Then
Else
Debug.Print "There is no data on worksheet ", ws.Name
End If

Set rng = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
```

### Final Functions() and Subs()

The stuff above is fine, but it would be cool if we could develop a function to return the True Used Range. I developed a few Functions() and Subs() to that end.

#### Function..GetUserSelectedCell

I would like to prompt the user to select a cell and test if there is any data on the worksheets the cell is located on. This makes my code more efficient before I continue processing.

Here I am using the InputBox of the Application Object with Type 8 parameter to allow the user to select a cell for the InputBox. More on the Application.InputBox Method (Excel).

```Public Function GetUserSelectedCell(strPrompt As String, _
strTitle As String) As Range

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'GetUserSelectedCell
'Returns a Range Object based on Cell user selects
'
'Parameters        :
'strPrompt         :    A string variable.
'                  :    Provide a question or statement to the user to take some action.
'strTitle          :    A string variable.
'                  :    Provide a title for the InputBox.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Declare variables
Dim rng                         As Range

'Users - select a cell on a worksheet
On Error Resume Next
Set rng = Application.InputBox( _
Prompt:=strPrompt, _
Title:=strTitle, _
Type:=8) 'Range selection

On Error GoTo 0

'Activate the worksheet
On Error Resume Next
rng.Parent.Activate

'Pass object to function
Set GetUserSelectedCell = rng

'Tidy up
If Not rng Is Nothing Then Set rng = Nothing

End Function
```

And here is how I call the Function in the final Sub()

```    'Prompt user to select a cell on a worksheet
Set rngUserCell = GetUserSelectedCell(strPrompt:="Please select a cell on a worksheet.", _
strTitle:="Get Cell Selection From User")
```

#### Function..What if the user clicked cancel?

The user may choose to cancel at the InputBox, so we need to handle that possibility. In this Function() I am using a MsgBox to ask the user if they wish to try again. More on the MsgBox Function.

```Public Function GetUserMessageResponse(strPrompt As String, _
strTitle As String, _
lngButtons As Long) As Long

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'GetUserMessageResponse
'Returns a value as a Long DataType
'
'Parameters        :
'strPrompt         :    Required. A string datatype.
'                  :    Provide a question or statement to the user to take some action.
'strTitle          :    A string datatype.
'                  :    Provide a title for the InputBox.
'lngButtons        :    A long datatype
'                  :    Use one of the vba button type enumerations
'                  :    vbOKOnly            0   OK button only                      <-Default value
'                  :    vbOKCancel          1   OK and Cancel buttons
'                  :    vbAbortRetryIgnore  2   Abort, Retry, and Ignore buttons
'                  :    vbYesNoCancel       3   Yes, No, and Cancel buttons
'                  :    vbYesNo             4   Yes and No buttons
'                  :    vbRetryCancel       5   Retry and Cancel buttons
'Information       :    The Message Box returns 1 of 7 values:
'                  :    vbOK        1
'                  :    vbCancel    2
'                  :    vbAbort     3
'                  :    vbRetry     4
'                  :    vbIgnore    5
'                  :    vbYes       6
'                  :    vbNo        7
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Declare variables
Dim MsgBoxValue             As Long

'Users - select a cell on a worksheet
MsgBoxValue = MsgBox( _
Prompt:=strPrompt, _
Buttons:=lngButtons, _
Title:=strTitle)

'Handle user actions
If MsgBoxValue <> vbYes Then
MsgBoxValue = vbCancel
End If

'Pass value to function
GetUserMessageResponse = MsgBoxValue

End Function
```

#### Function..TestForData

Now that I have a Cell on a Worksheet, I need to test to see if there is any data on the Worksheet. In the Function below, I first check Cell(1,1) for any data, if that does not contain any data, then I check the rest of the worksheet.

Here I am using the Find Method of the Range Object to search for anything on the Worksheet. More on the Range.Find Method (Excel).

```Public Function TestForData(ws As Worksheet) As Boolean

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'TestForData
'Returns a boolean datatype
'Checks to see that data exists in at least 1 Cell on a Worksheet
'
'Parameters        :
'ws                :   Required, A Woksheet Object.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Declare variables
Dim rng As Range

'Initialize values
TestForData = False

'Check the worsheet for data
On Error Resume Next
If Not IsEmpty(ws.Cells(1, 1)) Then
Set rng = ws.Cells(1, 1)
Else
With ws
Set rng = .Cells.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
End With
End If

'Update function value if the worksheet contains data
If Not rng Is Nothing Then TestForData = True

'Tidy up
Set rng = Nothing

End Function
```

And here is how I call the Function in the final Sub()

```    'Check if the worksheet has any data
blnFlag = TestForData(ws:=wsUserCell)
```

#### Function..Find First And Last Cells

So far, I have tested if the user clicked cancel or if the worksheet contains any data, at this point, I have passed those tests, so now I can get to the meat of it.

I have one Function to return either the first used Cell or the last used Cell – and that my friends is cool. I want my Functions() to be fast, efficient and flexible. I vary whether the Function() returns the last used Cell or first used Cell by passing a variable to the SearchDirection by using the values of the xlSearchDirection Enumeration: xlNext and xlPrevious.

When the SearchDirection is xlNext, the Function() returns the first used Cell. When the SearchDirection is xlPrevious, the Function() returns the last used Cell.

```Public Function GetCell(ws As Worksheet, _
rng As Range, _
lngDirection As Long) As Range

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'GetCell
'Returns a Range Object based on a single Cell
'The Cell is either the first Cell in a Range or the last Cell in a Range
'The distinction is based on the parameter value passed to lngDirection by the user as either xlPrevious or xlNext
'
'Parameters        :
'ws                :   Required, A Woksheet Object.
'rng               :   Required, A Range Object.
'lngDirection      :   Required, Either xlNext or xlPrevious.
'                      Use xlPrevious when searching for the last used Cell.
'                      Use xlNext when searching or the first used cell.
'
'Use               :   Find the last used Cell first
'                  :   Pass the last used Cell as a Range Object to the function to determine the first used Cell
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Get range as a single cell
With ws
Select Case lngDirection
Case xlNext
If Not IsEmpty(.Cells(1, 1)) Then
Set rng = .Cells(1, 1)
Else
Set rng = .Cells.Find(What:="*", _
After:=rng, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=lngDirection, _
MatchCase:=False)
End If
Case xlPrevious
Set rng = .Cells.Find(What:="*", _
After:=rng, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=lngDirection, _
MatchCase:=False)
End Select
End With

'Pass the range to the function
Set GetCell = rng

'Tidy up
Set rng = Nothing

End Function
```

When I call the Function() to get the last used Cell, I pass Cell(1,1) to the Function() and appropriate enumeration value for SearchDirection:

```            'Get last cell
Set rngLastCell = GetCell(ws:=wsUserCell, _
rng:=wsUserCell.Cells(1, 1), _
lngDirection:=xlPrevious)
```

When I call the Function() to get the first used Cell, I pass the Range Object created in the first call to the Function() and appropriate enumeration value for SearchDirection:

```            'Get first cell
Set rngFirstCell = GetCell(ws:=wsUserCell, _
rng:=rngLastCell, _
lngDirection:=xlNext)
```

#### The Final Sub()…GetTrueUsedRange

And here’s the final Sub() to bring it all together:

```Option Explicit
Sub GetTrueUsedRange()

'Declare variables
Dim wb As Workbook
Dim wsUserCell As Worksheet
Dim rngUserCell As Range
Dim rngStart As Range
Dim rngLastCell As Range
Dim rngFirstCell As Range
Dim rngTrueUsedRange As Range
Dim blnDataExists As Boolean
Dim lngMessageResponse As Long
Dim lngFirstCellRow As Long
Dim lngFirstCellColumn As Long
Dim lngLastCellRow As Long
Dim lngLastCellColumn As Long

'Initialize
Set wb = ThisWorkbook

'Prompt user to select a cell on a worksheet
Set rngUserCell = GetUserSelectedCell( _
strPrompt:="Please select a cell on a worksheet.", _
strTitle:="Get Cell Selection From User")

'Get the worksheet that contains the cell the user selected
If Not rngUserCell Is Nothing Then
Set wsUserCell = rngUserCell.Parent
Else
lngMessageResponse = GetUserMessageResponse( _
strPrompt:="The selected worksheet does not contain any data." & vbLf & _
"Or you clicked ""Cancel.""" & vbLf & _
"Would you like to try a different worksheet?", _
strTitle:="Missing Data Warning", _
lngButtons:=vbYesNo)
End If

'Check if the worksheet has any data
blnDataExists = TestForData(ws:=wsUserCell)

'If the worksheet does not have any data, ask the user to select a different worksheet or exit
If blnDataExists = False Then
lngMessageResponse = GetUserMessageResponse( _
strPrompt:="The selected worksheet does not contain any data." & vbCrLf & _
"Would you like to try a different worksheet?", _
strTitle:="Missing Data Warning", _
lngButtons:=vbYesNo)

If lngMessageResponse = vbYes Then
Call GetTrueUsedRange   'Recursive call
Exit Sub
Else
MsgBox "You clicked ""No"" or ""Cancel"". Now exiting.", vbInformation, "No Data Warning"
Exit Sub
End If
Else

'Get last cell
Set rngLastCell = GetCell(ws:=wsUserCell, _
rng:=wsUserCell.Cells(1, 1), _
lngDirection:=xlPrevious)
With rngLastCell
lngLastCellRow = .Row
lngLastCellColumn = .Column
End With

'Get first cell
Set rngFirstCell = GetCell(ws:=wsUserCell, _
rng:=rngLastCell, _
lngDirection:=xlNext)

With rngFirstCell
lngFirstCellRow = .Row
lngFirstCellColumn = .Column
End With

End If

'Create true used range
Set wsUserCell = wb.ActiveSheet
Debug.Print "Worksheet", wsUserCell.Name
With wsUserCell
Set rngTrueUsedRange = .Range(.Cells(lngFirstCellRow, lngFirstCellColumn), _
.Cells(lngLastCellRow, lngLastCellColumn))
End With

'Results

'Tidy up
If Not rngUserCell Is Nothing Then Set rngUserCell = Nothing
If Not rngStart Is Nothing Then Set rngStart = Nothing
If Not rngLastCell Is Nothing Then Set rngLastCell = Nothing
If Not rngFirstCell Is Nothing Then Set rngFirstCell = Nothing
If Not rngTrueUsedRange Is Nothing Then Set rngTrueUsedRange = Nothing
If Not wsUserCell Is Nothing Then Set wsUserCell = Nothing
If Not wb Is Nothing Then Set wb = Nothing
End Sub
```

### Tidy Up

I tested the Sub() on several different Worksheets with a variety of placement of data, no data and single cells of data. All tests returned correct expected results. Please let me know if your tests return incorrect results.

How do you find the True Used Range on your Worksheets?

## Bringing The Full Power of SQL To Bear In Excel

In my last post on using ActiveX Data Objects (ADO) with Excel VBA, I demonstrated some code to load a Recordset, filter the Recordset using the Recordset’s Filter Prpoerty, and copy the Filtered Recordset to a Worksheet using the CopyFromRecordset Method of the Range Object.

I put a link to the blog post on the Excel VBA and Users Group on LinkedIn. To our good fortune, James Wilson was reading. James responded with some nice comments and some very good code of his own. I was impressed and asked James if he would like to do a write up to post on the blog.

James kindly accepted my offer as follows in James’ words. Take it away James!

### Bringing the full power of SQL to bear in Excel

James Wilson
September 13, 2014

“I feel the need, the need for speed.” Top Gun

I love Excel, but sometimes you just want a bit more power to analyse your data. My favourite tool for analysing large quantities of data has always been SQL. While Microsoft includes MS Query in Excel out-of-the-box, it does have many limitations and is relatively slow. Using VBA and ADO is the next logical step.

The code below is the latest incarnation of a general purpose SQL function I’ve been using for the last five years or so. For me the data is the thing – I want to be able to start querying my data using SQL without having to start coding from scratch each time. Just copy and paste into a module in your workbook, and you’re ready to go.

Code first then some explanation:

```Function SQL(ByVal SQLstr As String, ByVal Destination As String, Optional ByVal ConnectionString As String) As Boolean

On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim myConnection As Object
Dim myRecordSet As Object
Dim myQueryTable As QueryTable

ThisWorkbook.Sheets(Destination).Activate
ThisWorkbook.Sheets(Destination).Cells.Delete

If ConnectionString = "" Then ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0"

myConnection.ConnectionString = ConnectionString
myConnection.Open
myRecordSet.ActiveConnection = ConnectionString
myRecordSet.Source = SQLstr
myRecordSet.Open

Set myQueryTable = Sheets(Destination).QueryTables.Add(Connection:=myRecordSet, Destination:=Range("'" & Destination & "'!a1"))
myQueryTable.Refresh

If myRecordSet.State <> adStateClosed Then myRecordSet.Close
If Not myRecordSet Is Nothing Then Set myRecordSet = Nothing
If Not myConnection Is Nothing Then Set myConnection = Nothing

Err.Clear
ErrorHandler:
If Err Then
Sheets(Destination).Cells(1, 1) = "SQL Error: " & Err.Description
SQL = False
Else
SQL = True
End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Function
```

I wrote this as a VBA function rather than a sub procedure, because I wanted to be able to trap errors in the calling procedure. It is a really a matter of preference whether you like your functions to return True or False (lines 28 and 30). I’ve chosen to return True if it worked, so I’d call it using coding something like:

```Sub test()
DidItWork = SQL("SELECT * FROM [Sheet1\$] WHERE [Sheet1\$].[date] > #01/03/2014#", "Sheet2")
If DidItWork = False Then … 'Do some error handling
End Sub
```

Basically this function sets up an ADO link to a data source, executes an SQL query on that data, and returns the results of that query to a worksheet of our choice in the current workbook using a query table.

When you call this function (line 1), you pass a string with your SQL query, a string with the name of the worksheet you want the data to be returned to, and optionally a connection string to the data source. By default the data source is the workbook the code is in. That’s right – you can use SQL to query data in other tabs in the same workbook (make sure your workbook is saved first).

If you find that most of the time you are querying a corporate database or other data source, then you’d just tweak line 15 of the coding to default to the connection string to the data source you are using most often.

Lines 4 to 6 and 42 to 44 are just the standard VBA codes that you’d put in to speed up any bit of coding. If you are calling this function and have these bits of coding in the calling procedure, then you can safely delete these lines from this function.

Lines 8 to 10 are to set up a local connection and recordset object (we’re going to use ADO to get our data), and a query table (which we are going to use to return the results of the SQL query to Excel).

The way this function is written, your data output is always going to be a worksheet in the current workbook in Cell A1. You can have no other data in this worksheet as Lines 12 to 13 delete the contents of the worksheet, before it is refreshed again with the query table set up in Line 26.

Line 17 to 27 is the meat of the function, setting up an ADO link and returning the data using a query table.

Lines 29 to 31 are to tidy up objects and connections. Line 36 is to give you a clue if you’ve made an error in your SQL.

So I’ve a personal library function that allows me to use SQL in Excel without much further thought – what do I do with it? Let me give a few simple examples to give an idea of the possibilities.

1. Treat my current spreadsheet a bit like a mini-database and run queries on it – that would be much harder to do just using VBA or manually copying and pasting.
2. Suck data out of multiple corporate databases and spreadsheets and join it together. You don’t need even to open the spreadsheets to get the data (as I said to start with – it’s all about speed and power). So for example let’s say you have one spreadsheet from your sales guys with sales volumes, and you have another spreadsheet with the confidential prices for each customer, then you can do a bit of SQL coding like:
DidItWork = SQL(“SELECT A.*, B.[Price], A.[Volume]*B.[Price] as [Revenue] from [C:\Sales Volumes.xlsx].[Data\$] A LEFT OUTER JOIN [F:\Prices.xlsx].[Sheet1\$] B ON A.[Product] = B.[Product] AND A.[Customer No] = B.[Customer] “, “Sales Forecast”)
So I’m using SQL aliases A and B for brevity, and by using multipart identifiers specifying the full path and filename of the Excel workbooks I can suck data out of any file I have access to. Note if you specify the data source fully, the connection string ADO uses is virtually irrelevant.
3. By using For…Next loops in VBA and a bit of text manipulation and the SQL command UNION I can consolidate multiple similar data sources simply. So using a string variable like below in a loop:

mySQLstring = mySQLstring & ” UNION ” & …
Good for consolidating budgets submitted in a similar format.

The limitation is really your knowledge of SQL. Beware of missing spaces and extra commas in your SQL if you are using the VBA & _ to join long strings together to form your SQL.

### Tidy Up

Thanks James – great job! How do you use ADO, SQL, Recordsets and QueryTables in your Projects?