Selecting a table row in a table using VBA

[bearcub]

Hi,

If I have a table and I want to highlight the table row (ala conditional formatting a row in a table) -- how would I do that?

I original thought of using the current range property but that will select the entire table not active table row where I have my cursor.

My first thought is to do something like this:

Range(Activecell.end(xlRight), Activecell.end(xlLeft). select

However, I don't think this would really work in VBA and there is probably a more efficient and correct what of doing this.

What am I doing wrong?

Michael

 

[Rick Rothstein]

Assuming your Table is named "Table1", this line of code will select the row for the ActiveCell...

[table="width: 500"]
[tr]
	[td]Intersect(ActiveCell.EntireRow, Sheet1.ListObjects("Table1").DataBodyRange).Select[/td]
[/tr]
[/table]

I guess you should test that the active cell is, in fact, inside the table before you execute the above line...

[table="width: 500"]
[tr]
	[td]If Not Intersect(ActiveCell, ActiveSheet.ListObjects("Table1").DataBodyRange) Is Nothing Then
  Intersect(ActiveCell.EntireRow, Sheet1.ListObjects("Table1").DataBodyRange).Select
End If[/td]
[/tr]
[/table]

 

[Caleeco]

Hello Michael,

When it comes to selecting cells, there are multiple ways you can do it. If you have a table.. lets say the table spans columns D to H, you can select the row using the code below.

Sub SelectRow()

ActiveSheet.Range("D" & ActiveCell.Row, "H" & ActiveCell.Row).Select


End Sub

This if for tables of a fixed width. Is your table fixed? or do you need it to be dynamic?

Hope that helps,
Caleeco

 

[bearcub]

thank you for the quick response.

I would like it to be dynamic and what happens if I don't have a table but just a number of rows of data (a list that hasn't been converted to a table)?

Michael

 

[Caleeco]

thank you for the quick response.

I would like it to be dynamic and what happens if I don't have a table but just a number of rows of data (a list that hasn't been converted to a table)?

Michael

Hello Michael,

In what context will the be used? Is it part of a larger macro?

When you say it needs to be dynamic... does the start column change??

Sub SelectRow()
ActiveSheet.Range(Selection.End(xlToLeft), Selection.End(xlToRight)).Select
End Sub

The above code will work in most circumstances (falls over if your active cell is at the start or end of the table and there are blank columns on that respective side)

Caleeco

 

[MARK858]

One ugly way....

Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column)).Select

 

[Rick Rothstein]

Assuming your "table" is set off from any other data that may be on the sheet, this should work...

Intersect(ActiveCell.EntireRow, ActiveCell.CurrentRegion).Select

 

[Caleeco]

^^ slick.... Saved to the code bank!

 

[MARK858]

A bit less ugly than what I posted earlier... seems ok.

Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, Columns.Count).End(xlToLeft)).Select

 

[My Aswer Is This]

That was a good answer. I tried working this and could not solve if.

Interesting how the title to the question was:
"Selecting a table row in a table using VBA "

But then OP said they had no Table.

Assuming your Table is named "Table1", this line of code will select the row for the ActiveCell...

[table="width: 500"]
[tr]
	[td]Intersect(ActiveCell.EntireRow, Sheet1.ListObjects("Table1").DataBodyRange).Select[/td]
[/tr]
[/table]

I guess you should test that the active cell is, in fact, inside the table before you execute the above line...

[table="width: 500"]
[tr]
	[td]If Not Intersect(ActiveCell, ActiveSheet.ListObjects("Table1").DataBodyRange) Is Nothing Then
  Intersect(ActiveCell.EntireRow, Sheet1.ListObjects("Table1").DataBodyRange).Select
End If[/td]
[/tr]
[/table]