CODEWITHSUNDEEP
vbaexcel
M_Idk392845
M_Idk392845

Reputation: 133

return a cell in the same row as activecell and first column of a selection in VBA

thanks in advance for your help. I'm new to VBA and I need to select the row header of an activecell -basically building this code, which doesn't seem to work in VBA:

Dim C as range ("I4")
Dim R as C.CurrentRegion
cells(C.row,R.Columns(1)).Select

I can't use C.End(xlToLeft) because I have another table on the left and, although there is one blank column dividing the 2 tables, this code jumps to the table on the left. Also, the first column of my table is not A, and I can't hardcode the column number. Many thanks if you can help! Silvia

Upvotes: 0

Views: 3544

Answers (1)

user3598756
user3598756

Reputation: 29421

from that little that can be understood in your post, I'd guess you're after

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

or

ActiveSheet.Cells(ActiveCell.row, ActiveCell.CurrentRegion.Columns(1).Column).Select

With no use of "helper" range variables (like yours C or R)

Should you need to use them, then the two alternatives become:

Set C = ActiveCell
Set R = C.CurrentRegion
Intersect(C.EntireRow, R.Columns(1)).Select

or

Set C = ActiveCell
Set R = C.CurrentRegion
ActiveSheet.Cells(C.row, R.Columns(1).Column).Select

but in any case you'd better not select anything and just:

  • set it to some range variable:

    Dim myCell as Range

set myCell = Intersect(ActiveCell.EntireRow, ActiveCell.CurrentRegion.Columns(1))

  • and then use it:

    myCell.Font.ColorIndex = 3

Upvotes: 2

Related Questions