Name
querytable
.ResultRange
Synopsis
Returns the range containing the results of the query. For example, the following code clears the results from a query table on the active worksheet:
ActiveSheet.QueryTables(1).ResultRange.Clear
If a query table has been created but not yet refreshed, accessing ResultRange
causes an error. There’s no direct way to test whether a query table has been refreshed. One solution to this problem is to write a helper function similar to the following to check if a query table has a result before accessing ResultRange
elsewhere in code:
Public Function HasResult(qt As QueryTable) As Boolean Dim ret As Boolean On Error Resume Next Debug.Print qt.ResultRange.Address If Err Then ret = False Else ret = True On Error GoTo 0 HasResult = ret End Function
Now, you can easily test if a query table has a result before clearing the result range or performing other tasks as shown here:
Set qt = ActiveSheet.QueryTables(1) If HasResult(qt) Then qt.ResultRange.Clear
Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.