range = range.value query

[thumbtwiddler]

Wondering if anyone can help. Have searched through and struggling to find this.

Going through some old proformas removing copy / paste values in the code and replacing with application.range("X") = application.range("Y").value to speed them up.

My problem is when (in the above example), X is a single cell and Y is a dynamic range of cells.

If you use copy / paste value, it would select cell X and paste across and down the same area as range Y but using =.value seems to only affect the single cell X rather than pasting the range in meaning almost all of range Y is unused.

I can go through and create temp named ranges that are the same size and then delete them but was wondering if anyone knew a better way of doing this?

If not, can anyone tell me what is the best approach for defining the size of Range Y when it is dynamic (ie, xlDown kind of situation) to then define the range starting from cell X for creating a temp name range. Looking at tagging start and end cells and then calculating the col / row from these but not sure if there's a better way of doing this.

Hope that makes sense - TIA

 

[SkipVought]

Hi,

...best approach for defining the size of Range Y when it is dynamic ...

Not exactly sure what you mean, but if you have a contiguous range...

dim rng as range

set rng = range(FirstCellObjectInRange, FirstCellObjectInRange.End(xldown))

works xlDown or xlToRight.

Naturally, remember to reference the sheet object when ambiguity can be an issue.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kjv1611]

For copying a range of cells into a new range based on a new cell, I believe you'll be better off sticking to the copy/paste via code. Either that, or you'll need to loop through the cells within the range to use the range = range.value method. That method works fine, but it'll take more lines of code to do what you want to do than the copy/paste methods.

--

"If to err is human, then I must be some kind of human!" -Me

 

[DJB10]

If these Y ranges are being created in code as well, if you store the start/end rows/cols into variables as you create them, you can then use those to size range X.

Range(cells(xStartRow,xStartCol),cells(xStartRow + yRowCount,xStartCol + yColCount).value = Y.value

Copy and paste might just be easier though