assign entire column to a string variable using VBA

Question

There is any possibility to store an entire column in a string variable? I need that variable as a string and not range type. I mean, something like this:

    Dim myStrVar As String
    myStrVar = Sheets("Sheet1").Range("A:A")
    MsgBox myStrVar

This code error is: Type mismatch I want to specify that my column contains only text (string type).

If possible, how can I do this?? Thank you!

What exactly are you trying to do? A Range is a series of cells with values so it makes sense you cant just assign them to a string since there is more than one (possibly over a million cells in 1 column depending on you version of Excel). Are you trying to concatenate the values of every cell? — psubsee2003
– psubsee2003, Commented Sep 18, 2014 at 9:10
@psubsee2003: I want to use all the cells information from that column to send an email with that information. So I need that variable that will be my email message. And yes, I want to concatenate all the cells content and store it in a variable — ghostlegend
– ghostlegend, Commented Sep 18, 2014 at 9:21

Yuvaraj Vaithinathan · Accepted Answer · 2014-09-18 09:42:02Z

1

You can do like this

a = 2
do while cells(a,1)<>""
mystrvar = mystrvar & cells(a,1)
a = a +1
msgbox mystrvar
loop

answered Sep 18, 2014 at 9:42

Yuvaraj Vaithinathan

393 bronze badges

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

Collectives™ on Stack Overflow

assign entire column to a string variable using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related