(VBA): Select cell at left side of activecell

Microsoft Office 2010 professional
May 5, 2011 at 22:13:51
Specs: Windows XP, Core 2 Duo 3.2 Ghz/2GB DDR3

I need to select a cell at the left side of the active cell using an offset, as I'm working with dynamic ranges. When I do this, i.e:
Range("B1").Offset(0, -1).Select
selects both B1 and A1 cells, but i want it to select only A1 cell.

Googled it but haven't found anything so far.

Any ideas?

See More: (VBA): Select cell at left side of activecell

Report •

May 6, 2011 at 05:33:30
I'm confused by your question.

First you said that you want to select a cell at the left side of the active cell but your code is specific to B1.

If I wanted to select a cell at the left side of the active cell I would use:

ActiveCell.Offset(0, -1).Select

That said, there's 2 other things I'd like to mention:

1 - When I use your code, it selects A1 only, not A1:B1.

Try this and tell us what you get:

MsgBox Range("B1").Offset(0, -1).Address

It should return $A$1.

2 - Why are you Selecting a cell? You rarely need to Select a cell via VBA in order to perform an operation on it.

For example:

Range("B1").Offset(0, -1).Select
Selection.Formula = "=B1+C1"

Can be reduced to:

Range("B1").Offset(0, -1).Formula = "=B1+C1"

You can typically perform the VBA operation directly on the Range from within VBA.

It's much more efficient/quicker.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

May 8, 2011 at 17:29:14
Thanks for the Reply Derby Dad, and sorry i set Range("B1") just for to see that it was on the second column (as example, but it was indeed more efficient to use Active Cell), sorry to cause confusion, but i guess you still got my point.

I want to select the cell at the left side to fill in a concatenate formula in order to use it as a search key, and i need to do it dynamically as the file has 20000+ rows and will be larger, so i cant fill the formulas for the entire column every time or the macro will take forever to execute. I tried the offset just as you saw, before posting this question but no good, as the selection was A1:B1, not just A1 as i the macro requires.

I'm going to try row count and see if i can work something out.

Thanks for the help.

Report •

May 8, 2011 at 20:45:54
As I said in my response, running this code selects A1 only. I don't know what you are running to have it select A1:B1, but in both Excel 2003 and 2010, this code only selects A1:

Sub SelectA1()
  Range("B1").Offset(0, -1).Select
End Sub

This code selects only the single cell left of the ActiveCell:

Sub SelectLeftCell()
  ActivCell.Offset(0, -1).Select
End Sub

Why your code is selecting more than one cell makes no sense.

Run this and tell me what the message box says:

Sub OneCellOrTwo()
 Range("B1").Offset(0, -1).Select
 MsgBox Selection.Address
End Sub

Finally, if you are trying to place a formula in 20,000 rows, it can be done in one line. This code will CONCATENATE Columns B and C, row by row, placing the results in Column A. In other words, Column A populated with 20000 formulas in an instant.

Sub FillFormula()
    Range("A1:A20000").FormulaR1C1 = "=RC[1]&RC[2]"
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question