Excel VBA Array list

Question

I am a toddler in VBA

I have a large range this could be more than 1000 text values (This could be going down A1), I am trying to concatenate all values with quote and comma into one cell (C1), i know of the transpose formula, but I am not sure my vba array will recognise this as a list.

I am keen for my array formula to recognize c1 as list, in order to carry out my action.

I am really keen to keep this clean and not use the concatenation and drag various formulas down.

I came across this, but this does not paste all the values into one cell.

Sub transpose()
Dim rng As Range
Dim ws As Worksheet
Dim last As Range

Set ws = ActiveSheet   
Set last = ws.Cells(Rows.Count, "A").End(xlUp)
Set rng = ws.Range("A1", last)

For Each cell In rng
    Dim hold As String
    hold = """"
    hold = hold + cell.Value
    hold = hold + """" + ", "
    cell.Value = hold
Next cell

rng.Copy
ActiveWorkbook.Sheets(2).Range("A1").PasteSpecial transpose:=True
End Sub

Code done by ryan E

If anyone can suggest any cheats on gathering list for Arrays that would be great. Other than using the Macro tool in excel

Example.

A1 = company1 A2 = company2 etc

Solution

C1 would show in one cell "company1", "company2", .... "company10000"

I am not sure I understand what you mean as a list in C1. Are you talking about some type of object like a ListObject (AKA Table) or rather just all the text values listed encapsulated by quotes and delimited by commas? If you're not familiar with the command there is the JOIN command in VBA that concatenates an array with delimiters. You can just grab the values from your range into an array and then join them and output to C1. — nbayly
– nbayly, Commented Nov 23, 2016 at 23:10
for Excel 2016 there is the TEXTJOIN function. Otherwise, you can copy the range and get the the text from the clipboard. — Slai
– Slai, Commented Nov 23, 2016 at 23:44

Tim Williams · Accepted Answer · 2016-11-24 22:32:13Z

5

You can use Join() and Transpose().

For example:

Sub transpose()

    Dim rng As Range
    Dim ws As Worksheet
    Dim last As Range

    Set ws = ActiveSheet

    Set last = ws.Cells(Rows.Count, "A").End(xlUp)
    Set rng = ws.Range(ws.Range("A1"), last)

    ws.Range("B1").Value = """" & Join(Application.Transpose(rng.Value), """,""") & """"

End Sub

EDIT: now I see what you really want to do (create an array of sheet names to pass to Sheets.Copy()) here's one approach...

Add a sheet named (eg) "Groups" to hold your various lists of sheets to be copied:

Group names are in Row 1, with a list of sheets below each name.

Then use this code:

'to demo the "CopySheets()" sub...
Sub Tester()

    CopySheets "Group2" 'copy all sheets in Group2

End Sub


'Create of copy for all sheets under "GroupName" header...
Sub CopySheets(GroupName As String)

    Dim rng As Range, arr
    Dim ws As Worksheet
    Dim f As Range

    Set ws = ThisWorkbook.Sheets("Groups") '<< has lists of sheet names

    'find the header for the group to be copied
    Set f = ws.Rows(1).Find(GroupName, lookat:=xlWhole)

    If Not f Is Nothing Then
        'found the header, so create an array of the sheet names
        Set rng = ws.Range(f.Offset(1, 0), ws.Cells(ws.Rows.Count, f.Column).End(xlUp))
        arr = Application.transpose(rng.Value)
        'use the array in the sheets Copy method
        ThisWorkbook.Sheets(arr).Copy

    Else
        'alert if you tried to copy a non-existent group
        MsgBox "Sheet group '" & GroupName & "' was not found!"
    End If

End Sub

edited Nov 24, 2016 at 22:32

answered Nov 23, 2016 at 23:14

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

19 Comments

user3287522 Over a year ago

Will this put it into one cell with quotes and comma

Tim Williams Over a year ago

Isn't that what you're looking to do ?

user3287522 Over a year ago

Tim, thanks, I will have to try the code out tomorrow but thank you for help

user3287522 Over a year ago

I am getting an error when I try to run this final result, Sheets(Array(Range("B1").Value)).Copy can anyone share some light run time error 9

Tim Williams Over a year ago

I wouldn't expect that to work. What exactly are you trying to do with that line? If you want to copy a bunch of sheets based on a list of sheet names then this wouldn't be a good approach.

|

Collectives™ on Stack Overflow

Excel VBA Array list

1 Answer 1

19 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

19 Comments

Your Answer

Sign up or log in

Post as a guest

Related