ListObject Subscript out of range error

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.

Please Login or Register  to view this content.

Thanks for the suggestion OllieB.

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

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, I will give that a shot. thanks for your help.

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