Select Active cell and other cells in the same column

DimebagCFH

New Member
Joined
May 7, 2017
Messages
2
Dear all,

First of all, I'd like to introduce myself because I've just discovered this interesting forum that probably would help me a lot to learn about Macros!
:)
I'm DimebagCFH and I work in a Quality Dept in automotive sector (Spain). So please, excuse my poor english level.


I'm not used to macros because I started this week to learn Excel macros and despite I know something from other language codes I don't know nothing from VB, but I can read and understand it the most.
I'd like to ask something that is getting on my nerves since yesterday, and I've been looking for it in loads of websites since I found these topic from this forum:
https://www.mrexcel.com/forum/excel-questions/27801-activecell-range.html

My question is this:
I need to write a simple macro that should select and copy some cells of the same row but different colum, and the columns are already known:
  • Select the actual cell.
  • Select the cell in +15 columns position.
  • Select the cell in +30 columns position.
  • Select the cell in +45 columns position.
Select this cells range, copy, and paste just the values in another sheet.
i.e:
Select the ("B5,B10,B15,B20"), etc. Not the ("B5:B20").

But maybe with just a brief example I'm able to continue by myself.


What I tryed is something like this:
Dim MyRange As Range
Set MyRange = Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Row, ActiveCell.Column + 2))
MyRange.Select
But what excel finnaly does is select from the active cell to the activecell+ 2 colums, all together.


Can anyone help me with this?

Many thanks in advance!


 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

eduzs

Active Member
Joined
Jul 6, 2014
Messages
476
Office Version
2019, 2010
Platform
Windows
range("B5,b10,b15,b20").Select

or

application.Union(cells(5,2),cells(10,2),cells(15,2),cells(20,2)).Select
 
Last edited:

AkaTrouble

Well-known Member
Joined
Dec 17, 2014
Messages
1,542
offset is another option without needing to hard code references


Code:
Range(ActiveCell.Offset(0, 5), ActiveCell.Offset(0, 10)).Select
 

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Hi

To select cells in the same row :-
Code:
Dim MyRange As Range
Dim I As Long

Set MyRange = Cells(ActiveCell.Row, ActiveCell.Column)

For I = 15 To 45 Step 15
    Set MyRange = Union(MyRange, Cells(ActiveCell.Row, ActiveCell.Column + I))
Next I

MyAddr = MyRange.Address
MyRange.Select
To select cells in the same column :-
Code:
 Dim MyRange As Range
 Dim I As Long

 Set MyRange = Cells(ActiveCell.Row, ActiveCell.Column)

 For I = 15 To 45 Step 15
     Set MyRange = Union(MyRange, Cells(ActiveCell.Row + I, ActiveCell.Column))
 Next I

 MyAddr = MyRange.Address
 MyRange.Select
hth
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,743
Office Version
2013
Platform
Windows
Normally it's not necessary to use "activecell" or "Select.

And your wanting to copy but you do not say where you want to paste these values.

Please explain what your wanting to do completely and maybe we could help you more.

Exact details are always ways to speed up getting answers here.

Like sheet names and column locations like "Column "B"
 

DimebagCFH

New Member
Joined
May 7, 2017
Messages
2
offset is another option without needing to hard code references


Code:
Range(ActiveCell.Offset(0, 5), ActiveCell.Offset(0, 10)).Select
Not works :(
Doing this the Range selected is from 0,5 to 0,10. What I need is to select (0,5)&(0,10)&(etc)

Hi

To select cells in the same row :-
Code:
Dim MyRange As Range
Dim I As Long

Set MyRange = Cells(ActiveCell.Row, ActiveCell.Column)

For I = 15 To 45 Step 15
    Set MyRange = Union(MyRange, Cells(ActiveCell.Row, ActiveCell.Column + I))
Next I

MyAddr = MyRange.Address
MyRange.Select
To select cells in the same column :-
Code:
 Dim MyRange As Range
 Dim I As Long

 Set MyRange = Cells(ActiveCell.Row, ActiveCell.Column)

 For I = 15 To 45 Step 15
     Set MyRange = Union(MyRange, Cells(ActiveCell.Row + I, ActiveCell.Column))
 Next I

 MyAddr = MyRange.Address
 MyRange.Select
hth
It works!
THANK YOU SO MUCH!

Normally it's not necessary to use "activecell" or "Select.

And your wanting to copy but you do not say where you want to paste these values.

Please explain what your wanting to do completely and maybe we could help you more.

Exact details are always ways to speed up getting answers here.

Like sheet names and column locations like "Column "B"
Hello!
I expent a couple of hours looking for how to do this, I just see tutorials that used the "activecell" or "select". As I said before I'm not used to macros and VB, I started this last week with it.:confused:

I didn't write nothing about the copy because it's just to copy and paste the values on sheet2, and I supposed that it would'n challenge me as the other part of the macro.


Regarding the main topic, I don't know the exact columns because I don't have here the excel file. The "Column B" was as example, because the column itself could change depending on what I need to copy.
But the question was the same, and seems that @ukmikeb solved my problem :)

I'll try it tomorrow as soon as I'll be on my desk.
 

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Normally it's not necessary to use "activecell" or "Select.

And your wanting to copy but you do not say where you want to paste these values.

Please explain what your wanting to do completely and maybe we could help you more.

Exact details are always ways to speed up getting answers here.

Like sheet names and column locations like "Column "B"
That's a harsh response to a VBA newbie!

Normally it's not necessary to use "activecell" or "Select.
Possibly, but without knowledge of the current position on a worksheet IMHO the ActiveCell is probably the best place to start.
Personally, I would consider it good practice for a newcomer to use Select while developing VBA modules until becoming proficient enough to be confident that the instructions he/she is creating are moving around the worksheet correctly.

@DimebagCFH Your first attempt was very good for a newbie, almost there.
Do come back to the forum if you have any further questions.
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Maybe one of these codes will get you going, both use sheet 1 and sheet 2.

The codes goes in a standard module, run either of them from sheet 1 or 2.

The first one gathers the cell values as in myRng = Sheets("Sheet1").Range("B5,B10,B15,B20") and lists them in a single row starting in column G with this line here Sheets("Sheet2").Range("G" & Rows.Count)...

The second code constructs the vaSrc (source) range and the vaTgt (target) ranges with Const sSrc$ = "B5,B10,B15,B20": Const sTgt$ = "F5,F10,F15,F20".
The source and target must be the same number of cells, but not the same "shape"

So, you can make changes the ranges and number of cells within some constraints.

Howard

Code:
Option Explicit
 
Sub Move_Scattered_Values_1() 

Dim myRng As Range, rngC As Range
Dim i As Long, numUp As Long
Dim myArr() As Variant
Dim rngSource As Range, rngTarget As Range

Set myRng = Sheets("Sheet1").Range("B5,B10,B15,B20")

For Each rngC In myRng
    ReDim Preserve myArr(myRng.Cells.Count - 1)
    myArr(i) = rngC
    i = i + 1
    
Next

Sheets("Sheet2").Range("G" & Rows.Count).End(xlUp)(2).Resize(columnsize:=myRng.Cells.Count) _
        = myArr                                  ' In one row across columns next empty row
 
End Sub



Sub Move_Scattered_Values_2()

  Const sSrc$ = "B5,B10,B15,B20": Const sTgt$ = "F5,F10,F15,F20"
  Dim n&, vaSrc, vaTgt
  
Dim wksSrc As Worksheet, wksTgt As Worksheet

Set wksSrc = ThisWorkbook.Sheets("Sheet1")
Set wksTgt = ThisWorkbook.Sheets("Sheet2")

  vaSrc = Split(sSrc, ","): vaTgt = Split(sTgt, ",")

  For n = LBound(vaSrc) To UBound(vaSrc)
     wksTgt.Range(vaTgt(n)) = wksSrc.Range(vaSrc(n))
  Next 'n

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,095,748
Messages
5,446,266
Members
405,392
Latest member
Steveoaktree1977

This Week's Hot Topics

Top