CODEWITHSUNDEEP
vbaexcel
Andy
Andy

Reputation: 3170

Macro to adjust an entire row in an Excel table at once

I need a way to programatically adjust an entire row in an excel table based on the ActiveCell, irrespective of what table it's in or where the table is. I was using 

ActiveSheet.ListObjects(1).ListRows(ActiveCell.Row - 1).Range.Select
Selection.Style = "Good"

but upon shifting the table down five rows, it now applies the action down a further five rows from the ActiveCell.

I've tried finding a way to replace the - 1 with some sort of - .HeaderRowRange.Row but nothing happens when I activate the macro.

Upvotes: 1

Views: 233

Answers (2)

Doug Glancy
Doug Glancy

Reputation: 27478

You can take advantage of the ActiveCell.ListObject property. This makes it more flexible, e.g., if there's two tables in a worksheet:

Sub FormatActiveTableRow()
Dim lo As Excel.ListObject

Set lo = ActiveCell.ListObject
If Not lo Is Nothing Then
    Intersect(ActiveCell.EntireRow, lo.Range).Style = "Good"
End If
End Sub

Upvotes: 2

basodre
basodre

Reputation: 5770

If I understood the problem correctly, try using the following code and report back:

ActiveSheet.ListObjects(1).ListRows(ActiveCell.Row - ActiveSheet.ListObjects(1).HeaderRowRange.Row).Range.Select

Upvotes: 2

Related Questions