Paste 1st blank column in range

[user21136]

Hi,

Trying to copy D3 from Sheet1 to the Range("D3:J3") on Sheet2.

Using the code below is OK except I have a value in K3

Sheets("Sheet2").Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Sheets("Sheet1").Range("D3").Value

Is there a way to copy/paste to the first blank column in range please?

Cheers, Glenn.

 

[user21136]

After a bit of searching, I've come up with this...

MsgBox [D3:J3].End(2).Address

This is finding the last entry in the range. Any idea how to offset to the next cell in the row D3:J3 please ie: the blank one.

Tried using .offset(1) but gets the cell below. Not sure why.

Cheers, Glenn.

 

[Rick Rothstein]

Give this a try...

MsgBox [D3:J3].End(2).Offset(,1).Address

although personally, I had the square bracket notation. I'm also not 100% sure what the (2) is doing (it does not correspond to one fo the preset constants I'm aware of). So in my own code I would write it like this...

MsgBox Range("D3:J3").End(xlToRight).Offset(, 1).Address

 

[user21136]

Give this a try...

MsgBox [D3:J3].End(2).Offset(,1).Address

although personally, I had the square bracket notation. I'm also not 100% sure what the (2) is doing (it does not correspond to one fo the preset constants I'm aware of). So in my own code I would write it like this...

MsgBox Range("D3:J3").End(xlToRight).Offset(, 1).Address

Thanks Rick!

Working

> I'm also not 100% sure what the (2) is doing

End can be one of 4 directions; Left,Right,Up,Down : So 2=Right

By the way, the range using either Range("D3:J3") or [D3:J3] is not restricted to those cells. It's only setting the row 3, not columns D to J. If I just use [3:3] it does the same thing.

I do have other stuff in A3, B3 and C3 so it doesn't matter and have limited the copy/paste so it doesn't go pass J3 anyway.

Cheers, Glenn.

 

[Rick Rothstein]

> I'm also not 100% sure what the (2) is doing

End can be one of 4 directions; Left,Right,Up,Down : So 2=Right.

According to the documentation for the End property, the four defined constants that can be used are... xlUp, xlDown, xlToRight, xlToLeft and their equivalent values are -4162, -4121, -4161, -4159... 2 is not one of those values; hence my confusion (still not sure why 2 works).

 

[user21136]

According to the documentation for the End property, the four defined constants that can be used are... xlUp, xlDown, xlToRight, xlToLeft and their equivalent values are -4162, -4121, -4161, -4159... 2 is not one of those values; hence my confusion (still not sure why 2 works).

Hi Rick,

There's a few words about it here...
http://www.mrexcel.com/forum/showpost.php?p=309202&postcount=14

Cheers, Glenn.