ListObject Subscript out of range error

[mtnbiker98]

Hello, All. Capture.JPG

I have the table in the image attached. I am trying to pull two adjacent cells out of a row and concat them together. I have several case statements before this for row numbers 2 - 4.

I have read that the header row is not included in the row count. i.e. row 1 should be the first non-header row in the table. However, I am not seeing that as the reality. When I use ListRows("2") I get data out of the first non-header row.

My problem is when i get to the case statement below. It says that ListRows("7") is out of range. I dont get it though. If ListRows("2") is the first non-header row and I want the last row in the table...It should be 7. I have included the table resize window as well so that you can see the bounds of the table. any help would be appreciated.

             Case ratingNum Like "5"
                Cells(2, 1).Value = ActiveWorkbook.Sheets("How to Use").ListObjects("RatingScale") _
                .Range(ActiveWorkbook.Sheets("How to Use").ListObjects("RatingScale").ListRows("7").Index, _
                ActiveWorkbook.Sheets("How to Use").ListObjects("RatingScale").ListColumns("Level Name").Index).Value _
                & ": " & ActiveWorkbook.Sheets("How to Use").ListObjects("RatingScale") _
                .Range(ActiveWorkbook.Sheets("How to Use").ListObjects("RatingScale").ListRows("7").Index, _
                ActiveWorkbook.Sheets("How to Use").ListObjects("RatingScale").ListColumns("Description").Index).Value

[OllieB]

Are you sure that you should not use ListRows(7)? I.e. without the quotes.

[mtnbiker98]

Thanks for the suggestion OllieB.

it didnt give a syntax error on that but, 7 was still out of range.

[OllieB]

If the ListRows collection is zero based then ListRows(6) would point to the last row, and ListRows(7) would be out of range

[mtnbiker98]

Right, totally agree. the problem is that ListRows(1) gives me the header row and ListRows(2) is the first non-header row. ListRows(0) is also out of range.

[OllieB]

*** duplicate post ***

[OllieB]

I have tried to replicate your problem by creating a table and referencing it from VBA. For my solution ListRows(0) points to the header and ListRows(6) refers to the last data row, I would suggest to split the single line of instructions wu=ithin the Case into separate statements to make it easier to debug what is going on. I suspect your problem may be elsewhere in the code

[mtnbiker98]

OllieB, I will give that a shot. thanks for your help.

[mtnbiker98]

ListRows(7) was still giving me "out of range" after recreating the table and validating the code. Not the cleanest solution but, I used .ListRows(6).Index + 1 and that returned the correct cells without the "out of range" error. Not the cleanest but, it works for my purposes.

Thanks for your help, OllieB