VBA get the cell value in a cell that is on the same row.

[decadence]

Hi, Is it possible to copy or get the cell value from a set range of cells which is a variable to a static range of cells that will never change but on the same row.

I am using this for my range variable but Columns("AA:AA") will be the static range

        Set Rng = Rng.Resize(Cells(Rows.Count, Rng.Column).End(xlUp).Row)

Example 1:

Form this
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Cell Value 1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Cell Value 2
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

To this
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD][/TD]
[TD]AA
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Cell Value 1
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Cell Value 2
[/TD]
[/TR]
</tbody>[/TABLE]

Example 2:

From this
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Cell Value 1
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Cell Value 2
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Cell Value 3
[/TD]
[/TR]
</tbody>[/TABLE]

To this

[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD][/TD]
[TD]AA
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Cell Value 1
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Cell Value 2
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Cell Value 3
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

Set rng = rng.Resize(Cells(Rows.Count, rng.Column).End(xlUp).Row)
rng.Copy Range("AA" & rng.Row)

 

[decadence]

This is perfect thanks Fluff

 

[Fluff]

Glad to help & thanks for the feedback