Concatenating dynamic ranges of cells

[n8Mills]

Hi all,

I wanted to pose this challenge as I've experimented and failed, let me start with my current process.

To concatenate two ranges (combining a contiguous range of cells from columns "A" and "B") into a single range of cells I'm using variations on this process:

With Range("A2", Range("A2").End(xlDown))
            .NumberFormat = "0"
            .FormulaR1C1 = "=RC[1]&RC[2]"
            .Copy
            .PasteSpecial Paste:=xlPasteValues
        End With

The "With" portion defines a dynamic range. The "FormulaR1C1" fills the cells with a formula returning the correct value, and the "Copy" / "Paste" steps hard-code those values.

I have found I can achieve a better result (through fewer steps) on a cell-by-cell basis with the following:

Range("A2") = Range("B2") & Range("C2")

But the only way I know to do this is by looping, which doesn't perform as well as the above method which can process a whole range in one "With" block.

As an experiment I attempted this:

Range("A2:A3") = Range("B2:B3") & Range("C2:C3")

But it doesn't work. If I can clear that hurdle I can apply it at least 20 times across my 3000 line macro, and get really complex. Can anybody help me?

[Paul_Hossler]

Not tested, but

set

[Paul_Hossler]

Ooops - wrong key

[Paul_Hossler]

Not tested, but something like

set r = Union (Range("B2:B3"), Range("C2:C3"))

Paul

[n8Mills]

Hmmm, nosir, didn't work, but I didn't get a "type mismatch" error that time. I assume that Union doesn't concatenate, it just puts all the values into a single range for selection, calculation, etc.

[Bob Phillips]

Why do you think it is a better way. Your original way works without a loop, so what is wrong with it?

[n8Mills]

For one, it doesn't work on filtered sheets. For that I either need to loop, or unfilter prior to Copy/PasteSpecial xlValues.

For two, I would think it's possible to accomplish the feat in a faster way, without writing data to the range twice.

Keep in mind the macro takes about 15-20 minutes to run and I'm trying to pare that down. The more professional I can make it the more likely I can convince the office on the East coast to adopt it.

[Bob Phillips]

Well you could always remove the filter, looping is going to slow you down even more.

But shouldn't you be tackling the 15-20 mins rather than worry about the new stuff? Do you know where it takes the time?

[n8Mills]

It's a series of stuff like this. I figure if I can cut my write times in half the macro will run much faster.

I've got it working as a model and have begun the refactoring process, leaning it out, standardizing the calculation processes and bulletproofing it with error trapping. If I'm going to improve it, now is the time. If it's not possible or the benefits are negligible I'll just move on, but I wanted to give it the ol' Community College try.

Maybe it be possible to assign the values to memory, then write the values to memory, like this:

With Range("A2", Range("A2").End(xlDown)) 
        .NumberFormat = "0" 
        rangeVals = "=RC[1]&RC[2]" 
        .Value = rangeVals 
End With

No, that does the same thing. Thing is, I need to simulate the cell in logic. Is there a way to change the formulas to hard values without The .Copy / .PasteSpecial xlValues?

I need something like this:

Range("A2", Range("A2").End(xlDown)).Value = _
        Application.WorksheetFunction.Calculate("=RC[1]&RC[2]")

The big issue being that the Worksheetfunction "Calculate" doesn't exist.

[mikerickson]

This will fill D3: D10 with the concatenation of B3:B10 and C3:C10.

With Range("B3:C10"): Rem range containing data
    .Offset(0, .Columns.Count).Columns(1).Value _
    = Evaluate("=" & .Columns(1).Address & "&" & .Columns(2).Address)
End With

[Bob Phillips]

Don't get me wrong, efficiency is a laudable aim. But my point is that you should address the current lack of efficiency before adding more code, it m ay require a re-design, which would change how you add the extra functionality.

The first code you posted doesn't work anyway, as it adds B&C not A& B.

In the meantime, chew on this

Dim rng As Range
Set rng = Range("A2", Range("A2").End(xlDown))
With rng
    .NumberFormat = "0"
    .Value = Parent.Evaluate("IF(ROW()," & rng.Address & "&" & rng.Offset(0, 1).Address & ")")
End With

[n8Mills]

Well, I was drafting a thank-you to Mike for enlightening me on "Evaluate", but when I got back from my dinner the kids had helpfully closed my browser. So now I have 2 takes on the topic thanks to xld.

I plan to go ahead with this code as it works, is compact, an I understand how to manipulate it:

With Range("A2", Range("A2").End(xlDown))
    .Value = Evaluate(.Offset(0, 1).Address & "&" & .Offset(0, 2).Address)
End With

Thanks, guys, for your help, it's exactly what I was hoping for. I think the thing that hung me up in the past with the Workbookfunction was that I was not tacking ".Address" onto the end of my ".Offset".

Again, many thanks. Do you guys see any issue with me simplifying it to this degree? That is, do you foresee any problems?

[david000]

That is, do you foresee any problems?

The only potential problem is if you or the user does any switching of reference style, i.e., xlA1 will work xlR1C1 will not work.

[Bob Phillips]

Well, I was drafting a thank-you to Mike for enlightening me on "Evaluate", but when I got back from my dinner the kids had helpfully closed my browser. So now I have 2 takes on the topic thanks to xld.

I plan to go ahead with this code as it works, is compact, an I understand how to manipulate it:

With Range("A2", Range("A2").End(xlDown))
    .Value = Evaluate(.Offset(0, 1).Address & "&" & .Offset(0, 2).Address)
End With

Thanks, guys, for your help, it's exactly what I was hoping for. I think the thing that hung me up in the past with the Workbookfunction was that I was not tacking ".Address" onto the end of my ".Offset".

Again, many thanks. Do you guys see any issue with me simplifying it to this degree? That is, do you foresee any problems?

So you really did want columns B & C!

I don't see that this is any better than the copy method. I have just timed it on 15,000 rows and got these numbers

Evaluate: 36464.859212963
Copy: 36464.9842013889

AN interesting technique, yes. Better than what you had, not really. As I said, you should be looking at that 15-20 mins.

[Bob Phillips]

The only potential problem is if you or the user does any switching of reference style, i.e., xlA1 will work xlR1C1 will not work.

That is easily overcome by setting to xlA1 and reset back afterwards.

But ... the copy method doesn't care :-)

[mikerickson]

This addresses both the reference style issue and the situation where colA is empty,
putting Range("A2").End(xldown) at the bottom of the sheet and slowing it down.

With Range("A2", Range("A2").End(xlDown))
    With Application.Intersect(Range(.Parent.Range("A1"), .Parent.UsedRange).EntireRow, .Cells)
        .Value = Evaluate(.Offset(0, 1).Address(, , Application.ReferenceStyle) _
                                  & "&" & _
                                 .Offset(0, 2).Address(, , Application.ReferenceStyle))
    End With
End With

[n8Mills]

xId, as I've said in the past I'm using this technique in multiple locations. It's about minimizing code as much as it is about performance. After all, somebody is expected to evaluate what I've done and maintain it. Granted, the code is harder to understand, but if the macro is 2000 lines as opposed to the current 3000+ then I'm happy.

Mike, I didn't want to con volute the question with the continuous range issue, but since you brought it up I actually am using

Range("A2:A" & Range("B65536").End(xlUp).Row)

(Where Column "B" never has blank cells) to get my "write-range"