	AllExperts > Excel
	Search

About Tom Ogilvy
Expertise
Worked with the program for many years - provided assistance on MS Excel Newsgroups since 1997. Have received the Microsoft MVP award annually since 1999. I don't answer questions on using Excel in a browser Since I have no way to test this. Prefer not to answer charting questions. I consider myself to be particularly knowledgeable about using VBA internal to Excel but have no problems with formulas and pivot tables either.

Experience
Have Used Excel for 15 - 20 years. Answered in excess of 70,000 Excel related questions in MS Excel newsgroups. Unless obvious, please specify whether you want a worksheet function or macro/VBA solution.

Education/Credentials
BS General Engineering (concentration in Industrial Engineering) MS Operations Research Systems Analysis

You are here: Experts > Computing/Technology > Microsoft Software > Excel > How do I change the active cell in a non contiguous range or selection?

Excel - How do I change the active cell in a non contiguous range or selection?

Expert: Tom Ogilvy - 11/9/2009

Question
QUESTION:
I know that "tab" "enter" etc change the active cell in a non contiguous range in the spreadsheet .

How do I get a macro to change the active cell in a non contiguous range or selection?

Thanks for any suggestions.
Flip

ANSWER: Flip,

just activate the cell you want to be the activeCell. As long as it is in the current selection, the selection will remain the same and the activecell will be changed. Here is a demonstration from the immediate window:

set r = Range("A1:B10,D1:E10,G1:H10")
r.select
? selection.address
$A$1:$B$10,$D$1:$E$10,$G$1:$H$10
? activecell.Address
$A$1
Range("G1").Activate
? selection.Address
$A$1:$B$10,$D$1:$E$10,$G$1:$H$10
? activecell.Address
$G$1

so in this case, a non-contigous range was selected of Columns A:B, D:E, G:H from rows 1 to 10 and by default, the top left corner was the activecell (cell A1).

the command

Range("G1").Activate

changed the activecell to cell G1.

--
Regards,
Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Tom,
Thanks for the quick reply.
I should be a bit more specific:
The purpose of the macro is to switch the contents of 2 non contiguous cells that will be selected by the person running the macro. The possible combination of cells is >20,000 so I will have to pick up the cells from the selection.

An example ... Given that:
a1 = tom
b6 = roy
both a1 and b6 are selected and b6 was selected 2nd

Then the following puts tom in var1 and roy in var2 and writes the contents of var1 to the active cell which is the second cell of the selection.
  var1 = Application.Selection
  var2 = ActiveCell
  ActiveCell.Value = var1

What I can't figure out now is how to tell it to change the active cell back to the other cell in the selection so that I can write the contents of var2 to it.

Any thoughts on this?

Thanks again
ptroiano

Answer
ptroiano

There is no reason to select the cells or change the activecell

v = Range("A1").Value
Range("A1").Value = Range("B6").Value
Range("B6").Value = v

so I never select either cell

If you want the user to select two cells and then run the macro to switch the contents

Sub AAA()
Dim r1 As Range, r2 As Range, cell As Range, v As Variant
If Selection.Cells.Count <> 2 Then Exit Sub
Set r1 = ActiveCell
For Each cell In Selection
If cell.Address <> r1.Address Then
    Set r2 = cell
    Exit For
End If
Next
' Now swap the values
v = r1.Value
r1.Value = r2.Value
r2.Value = v

End Sub

that would pay attention to what is the current active cell, but it really doesn't make any difference which one is the activecell if your are going to swap the values

Sub AABB()
If Selection.Cells.Count <> 2 Then Exit Sub
If Selection.Areas.Count = 2 Then
v = Selection.Areas(1)
Selection.Areas(1).Value = Selection.Areas(2).Value
Selection.Areas(2).Value = v
ElseIf Selection.Areas.Count = 1 Then
v = Selection(1).Value
Selection(1).Value = Selection(2).Value
Selection(2).Value = v
End If

End Sub

the last two macros have been tested and worked fine for swapping values in two cells whether they are contiguous or not contiguous.

--
Regards,
Tom Ogilvy

Ask a Question

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here