Variable Table Range Definition in VBA

[londonparisrome]

I am self-taught in VBA, so my apologies if this is an easy question. I am trying to reset data in the first column of a table to "Y". The current table location is rows 16-246, but I would like the user to be able to expand that, as needed, so I need the row numbers to be variable. I was able to create the variable range on the spreadsheet in cell X2, so it contains "(B16:B246)", and it will grow or shrink based on the rows in the table. Is there a way to reference cell X2 in my code for the range, or am I going about it the wrong way?

Private Sub Reset_Click()
' Replace column B with Y
ActiveSheet.Range("B16:B246").Value = "Y"
End Sub

 

[Norie]

Are you dealing with an actual table?

If you are there are various ways you can refer to a column in the table.

Sheets("Sheet1").ListObjects("Table1").ListColumns("Field1").DataBodyRange.Value = "Y"

 

[londonparisrome]

Are you dealing with an actual table?

If you are there are various ways you can refer to a column in the table.

Sheets("Sheet1").ListObjects("Table1").ListColumns("Field1").DataBodyRange.Value = "Y"

Yes. It's a table

I must be doing something wrong in the syntax. I keep getting an error. I have tried the following options. I am in Sheet 10, and the table name is combined:

ActiveSheet.ListObjects("Combined").ListColumns(2).DataBodyRange.Value = "Y"

ActiveSheet.ListObjects("Combined").ListColumns("Field2").DataBodyRange.Value = "Y"

Sheets("Sheet10").ListObjects("Combined").ListColumns("Field2").DataBodyRange.Value = "Y"

 

[Norie]

What's the error, where are you putting the code and what's the column name?

 

[londonparisrome]

Thanks for your help. I received the following error:

 

[Norie]

That usually means that either there is no table named 'Combined' on the active sheet, or in that table there is no column named 'Field2'.

In the code I posted I used generic/default names for the table/column, you would need to change those to reflect the actual table/column names you are working with.

 

[londonparisrome]

That usually means that either there is no table named 'Combined' on the active sheet, or in that table there is no column named 'Field2'.

In the code I posted I used generic/default names for the table/column, you would need to change those to reflect the actual table/column names you are working with.

Thank you!