VBA Tips - Concatenate Range

Hi

First off this is an awesome resource and forum so thank you.

What I'm trying to do has 4 columns:

Column A: This has two data ranges placed in it, separated by a few blank lines.

Column B: I need the second range, which is also made up of worded hyperlinks (starting at A:30 to A: ?), to be duplicated here for a specified amount (I'll specify the amount because it continually changes, maybe a input box would work here?). So it'll be list of the same words repeated one below the other.

Column C: I need to concatenate the duplicated word list in Column B with the 1st range in Column A (Starting at A3:A?, usually there aren't that many maybe between 4 and 12 words but it will vary). The concatenation needs to start on the second word of the Column B list, see the example.

Column F: Will have hyperlinks aligned on the same row it's corresponding new word in Column B. Being able to have a macro that separates the 2nd range word links in Column B to coincide with the first new word of the same name would be helpful. Also being able to remove the hyperlink from the 2nd data range in Column A afterwords would also be great but not essential as I can do this manually.

Here's an example of what it would look like:

Column A Column B Column C Column F Brown Dog http://www.animals.com/dog Black Dog Dog Brown Tan Dog Dog Black White Dog Dog Tan Brindle Dog Dog White Dog Dog Brindle Cat http://www.animals.com/dog Cat Cat Brown Cat Cat Black Dog Cat Cat Tan Cat Cat Cat White Horse Cat Cat Brindle Gerbil Horse http://www.animals.com/horse Horse Horse Brown Horse Horse Black " "





....And so on


I previously recorded an entire manual process which works, but I had to create individual macros for each number of duplicates I was making, for example 4 duplicates, 5 duplicates. I could change each individual range for a new duplicate number but it would be time consuming (takes just as long as doing it manually). I now have a macro which solves the Column B problem, but if anyone can help with either more efficient code or add ons that would be appreciated.

VB:

Sub ContextualItems() 
     
    Dim Repeats As Long 
    Dim FirstRow As Long 
    Dim LastRow As Long 
    Dim RowCount 
    Dim r As Long 
     
     
    Repeats = 12 
    FirstRow = 30 
    LastRow = Range("A" & Rows.Count).End(xlUp).Row 
    RowCount = Repeats * (LastRow - FirstRow + 1) 
     
    Range("A30:A87").Copy Destination:=Range("B2").Resize(RowCount) 
     
    Columns("B:B").Select 
    ActiveWorkbook.Worksheets("content_theme_ideas_20110811_06").Sort.SortFields. _ 
    Clear 
    ActiveWorkbook.Worksheets("content_theme_ideas_20110811_06").Sort.SortFields. _ 
    Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, _ 
    DataOption:=xlSortNormal 
    With ActiveWorkbook.Worksheets("content_theme_ideas_20110811_06").Sort 
        .SetRange Range("B2:B697") 
        .Header = xlNo 
        .MatchCase = False 
        .Orientation = xlTopToBottom 
        .SortMethod = xlPinYin 
        .Apply 
    End With 
     
    Dim rng As Range 
    Set rng = Sheet1.Range("A4:A14") 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



I understand it may not very clear so please ask if you have an issue with my question (I'm a VBA noob! )

Thanks in advance.

Hi All, is there a easier to combine my data without having to type it out like so: A1&","&A2&","&A3.

My range is around 100-150 cells..

Thanks in advance.

Hello,

I have a table that looks like this:

.......2008 2009 2010
jan
feb
mar
...

filled with numbers. I want to be able to use the NPV function on these range, but NPV requires the date to be linear. How can I 'concatenate' the ranges so the effect would be similar to having NPV (wacc,b2:b12+c2:c12+d2:d12)?

It would be ideal to keep my spreadsheet macro-free

Thanks in advance... OP

Anyone know an easy way to concatenate a range of cells?
ie A1:A1500

I can do =concatenate(a1,a2,a3,a4,a5,a6,etc) but this would take ages.

Thanks for your help

J

is there anyway to concatenate a range of cells in one column? I have about 100 cells i would like to combine into one cell with spaces in between.

Thanks

This may seem strang but I do have a good reason - how can I concatenate a range of columns when there are more than the allowed max in the function i.e. over 100 columns of information?

I have variable "alue" which is range:
alue = "C" & alkurivi & ":" & "C" & r

Let say that alkurivi is 4 and r is 9.

How I concatenate cells C4:C9 to cell B4?

Is there a way to concatenate a range of cells?

I have a list of data of about 3000 cells in a column in excel.. I would like to combine these cells in groups of about 500.

Is there any way to accomplish this?
Thanks

Hi All,

I want to concatenate the text from two separate columns, with a space between the text. The columns are dynamic ranges, but the number of rows will always be the same. The part of the code I'd hoped would do it this a
Code:

 
Dim LR As Long
LR = Range("A:Q").Find(what:="*", LookIn:=xlValues, _
             SearchDirection:=xlPrevious).Row
Range("Q2:Q" & LR) = Range("M2:M" & LR) & " " & Range("L2:L" & LR)

I keep getting a "type mismatch" error on the last line.
...any suggestions? thanks in advance.

I have a fixed cell range ($Y$13:$Y$25) with dynamic data.

The text "Grand Total" can appear in any one of these cells.
For example, lets say cell $Y$16 = "Grand Total", all the cells after $Y$16 i.e ($Y$17:$Y$25) will have value = "0"

I'm trying to concatenate the data(text) in cells $Y$13:$Y$16 into a single cell.

I'm trying to use index and match to find the valid range of cells $Y$13:$Y$16, but not been successful to concatenate.

I'm using MATCH("Grand Total",Y13:Y25,0) to find cell $Y$17 so I know the range is Y13:Y16

How can I concatenate the data in Y13:Y16?