Select Active cell and other cells in the same column

[DimebagCFH]

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!

 

[eduzs]

range("B5,b10,b15,b20").Select

or

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

 

[AkaTrouble]

offset is another option without needing to hard code references

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

 

[ukmikeb]

Hi

To select cells in the same row :-

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 :-

 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

 

[My Aswer Is This]

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]

offset is another option without needing to hard code references

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 :-

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 :-

 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.

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]

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]

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

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