Home » Questions » Computers [ Ask a new question ]

Excel select column

Excel select column

In excel, if I want to perform a function with the column to the left, how do I go about this?

Asked by: Guest | Views: 30
Total answers/comments: 2
Guest [Entry]

"Here's how to reference the cell in the column to the left in a worksheet function:

=INDIRECT(ADDRESS(ROW(),COLUMN()-1))

there are probably other ways to do it, but I haven't found a good, terse way to self-reference an Excel cell.

To reference the entire column to the left in a worksheet function:

=INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN()-1,4),""1"","""") & "":"" &
SUBSTITUTE(ADDRESS(1,COLUMN()-1,4),""1"",""""))"
Guest [Entry]

"Since this is a programming question site, I assume you mean in VBA.

The ActiveCell is the currently marked cell (or range), ActiveCell.Column will give you the column number (leftmost column for a range if memory serves me correctly) that you can use.

Simply subtract one and use it (assuming it's not 1 already, of course). If you want the actual column letter (you don't need it for calculations but may want it anyway), use:

Function GetCol (ByVal rng As Range)
If rng.Column < 27 Then
GetCol = Chr (64 + rng.Column)
Else
GetCol = _
Chr (64 + (rng.Column - 1) \ 26) + _
Chr (65 + (rng.Column - 1) Mod 26))
End If
End Function

along with:

Dim colStr as String
colStr = GetCol (ActiveCell)
MsgBox (colStr)

If this isn't a VBA question, it doesn't really belong here (although I see by one of your comments that you're talking about running a function, so I guess you did mean in VBA after all)."