G
Gregory Kip
Here is a stumper.
Given:
In a single Workbook, WB1, I have two Worksheets, WS1 and WS2.
In WS1 I have a QueryTable, QT1, with the following properties:
is a Text file import, tab delimited, etc.
refreshes on file open
does NOT refresh periodically
DOES allow manual refresh
is set to "Overwrite existing cells with new data, clear unused
cells".
cells."
In addition to the named Range that Excel creates for QT1, I have a named
Range, N1, that refers to a single column of the data imported by QT1. I
adjust N1.RefersTo in QT1_AfterRefresh as follows:
'Update N1 to span column A of QT1.
Private Sub QT1_AfterRefresh(ByVal Success As Boolean)
Dim Query_Data_Count, First_Data_Row, Last_Data_Row
'QT1 is declared and set in my 'Events' Class Module.
Query_Data_Count = QT1.ResultRange.Rows.Count
First_Data_Row = IMP3_QT.ResultRange.Row
Last_Data_Row = Query_Data_Count + First_Data_Row - 1
Application.Names("N1").RefersTo = "='" + WS1.Name + "'!$A$" +
CStr(First_Data_Row) + ":$A$" + CStr(Last_Data_Row)
End Sub
Moreover, in Module1 I have the following function:
'Count the visible rows.
Function Visible_Count (R As Range) As Long
Dim C As Range, Count As Long
For Each C in R
If Not C.EntireRow.Hidden Then 'The Error is raised here. Using
SpecialCells() raises the same error.
Count = Count + 1
End If
Next C
Visible_Count = Count
End Function
In some cell in WS2 I have the formula:
=Visible_Count(N1)
Here is the problem: when I open the file Excel executes =Visible_Count(N1)
which raises Error 1004 (Unable to get the Hidden property of the Range
class) at the marked line. Here is the rub: when I then manually calculate
the cell, the error is not raised and I get the correct value. Furthermore,
after manual calculation, I can execute filters on Column A of the data
import and the formula works perfectly. If I manually refresh the data, the
error is raised.
I have worked around the problem by turning off automatic calculation in
QT1_BeforeRefresh, and turning it back on at the end of QT1_AfterRefresh.
But I would like to address the underlying problem, if possible. Does it
have something to do with asynchronous/synchronous query refreshing? Or
perhaps with the "Overwrite existing cells..." property? Or perhaps the
range is somehow disabled as the query is refreshed?
Please help. Thanks!
Given:
In a single Workbook, WB1, I have two Worksheets, WS1 and WS2.
In WS1 I have a QueryTable, QT1, with the following properties:
is a Text file import, tab delimited, etc.
refreshes on file open
does NOT refresh periodically
DOES allow manual refresh
is set to "Overwrite existing cells with new data, clear unused
cells".
cells."
In addition to the named Range that Excel creates for QT1, I have a named
Range, N1, that refers to a single column of the data imported by QT1. I
adjust N1.RefersTo in QT1_AfterRefresh as follows:
'Update N1 to span column A of QT1.
Private Sub QT1_AfterRefresh(ByVal Success As Boolean)
Dim Query_Data_Count, First_Data_Row, Last_Data_Row
'QT1 is declared and set in my 'Events' Class Module.
Query_Data_Count = QT1.ResultRange.Rows.Count
First_Data_Row = IMP3_QT.ResultRange.Row
Last_Data_Row = Query_Data_Count + First_Data_Row - 1
Application.Names("N1").RefersTo = "='" + WS1.Name + "'!$A$" +
CStr(First_Data_Row) + ":$A$" + CStr(Last_Data_Row)
End Sub
Moreover, in Module1 I have the following function:
'Count the visible rows.
Function Visible_Count (R As Range) As Long
Dim C As Range, Count As Long
For Each C in R
If Not C.EntireRow.Hidden Then 'The Error is raised here. Using
SpecialCells() raises the same error.
Count = Count + 1
End If
Next C
Visible_Count = Count
End Function
In some cell in WS2 I have the formula:
=Visible_Count(N1)
Here is the problem: when I open the file Excel executes =Visible_Count(N1)
which raises Error 1004 (Unable to get the Hidden property of the Range
class) at the marked line. Here is the rub: when I then manually calculate
the cell, the error is not raised and I get the correct value. Furthermore,
after manual calculation, I can execute filters on Column A of the data
import and the formula works perfectly. If I manually refresh the data, the
error is raised.
I have worked around the problem by turning off automatic calculation in
QT1_BeforeRefresh, and turning it back on at the end of QT1_AfterRefresh.
But I would like to address the underlying problem, if possible. Does it
have something to do with asynchronous/synchronous query refreshing? Or
perhaps with the "Overwrite existing cells..." property? Or perhaps the
range is somehow disabled as the query is refreshed?
Please help. Thanks!