1

I have a worksheet change event that performs an action when each cell of a table column changes. For that specific action, I need to find the row number of the target cell. I have the following snippet inside the Worksheet_Change event:

If Not Intersect(Target, tbl.ListColumns(5).DataBodyRange) Is Nothing Then
    Application.EnableEvents = False
    row_num = tbl.ListRows(Target.Row - tbl.HeaderRowRange.Row).Index
    Application.EnableEvents = True
End If

I have a separate macro that just refreshes this same table on a button click.

Sub Refresh()

Application.EnableEvents = False
ActiveSheet.ListObjects("Table1").Refresh
Application.EnableEvents = True

End Sub

The macro runs fine and returns the right row_num. However, only when the table is refreshed, the Target.Row value in the 1st snippet is set to the same row number as the Header row of that table, thus making row_num as empty and returning a 'Subscript out of range error.

Why is this happening? By specifying that the action be performed only when the target cell intersects the DataBodyRange of that table column, should it not work only for changes in the data part of the column (excluding header and totals)?

Improve this question
9
  • Just to clarify, so if you add a Debug.Print Target.Row (outside the loop), it doesn't return the correct row number? Commented May 21, 2019 at 14:04
  • It returns the sheet row number. Commented May 21, 2019 at 14:12
  • Right, but you say that Target.Row is the same row number as the header row of the table. Is that true or not? In other words, do you have sample values for Target.Row and tbl.HeaderRowRange.Row? Commented May 21, 2019 at 14:14
  • I should clarify that only when the table is refreshed that it crashes - Target.Row is somehow detected as the Header Row cell and an error is returned because of the way row_num (the table row number) is calculated and it results in row_num=0. The macro runs fine if I don't refresh - Target.Row returns the sheet row number of the changed cell, using which the right row_num is identified. Commented May 21, 2019 at 14:27
  • Do you refresh it within the Worksheet change event? Commented May 21, 2019 at 14:30

1 Answer 1

Reset to default
0

So I have to rescind my initial answer after testing and go a completely different way.

By specifying that the action be performed only when the target cell intersects the DataBodyRange of that table column, should it not work only for changes in the data part of the column (excluding header and totals)?

Correct, this portion of code will not run when the Target is outside the DatabodyRange of the 5th Column in Table1

Application.EnableEvents = False
row_num = tbl.ListRows(Target.Row - tbl.HeaderRowRange.Row).Index
Application.EnableEvents = True

However, only when the table is refreshed, the Target.Row value in the 1st snippet is set to the same row number as the Header row of that table, thus making row_num as empty and returning a 'Subscript out of range error.

Exactly. Assuming what you say is true and Target.Row = tbl.HeaderRowRange.Row after refresh. Then your code is designed such that row_num will not be assigned because the code above will not execute.

Why is this happening?

The only way I can get your code to generate an out of range error is when I do what @BigBen alluded to in his final comment. If I select a sell in or above the header row and then expand my selection to include a cell in the 5th column data bodyrange then I get your error. You can test if this is is your problem with Target.Cells.Count.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

But the problem line is row_num = tbl.ListRows(Target.Row - tbl.HeaderRowRange.Row).Index...
That's not what I read. I read "row_num is empty", the error location was ambiguous.
Well, the only line that has row_num in it is the one I mentioned... It's also the only line with Target.Row in it.
Right, but I think the error happens above it, when the condition is being tested.
I'm not sure that line can throw a subscript out of range error.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.