We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

VBA, Range Variable into String Variable. Diaplay elements of Range

Medium Priority
315 Views
Last Modified: 2012-05-11

Hi guys

I would like to choose a Range from Excel worksheet and then be able to msgbox the result in a string like this:
'[Workbook.xls]Sheet1'!$A$1:$A$4

To include the name of the workbook file as well.

The Code goes like below but I need to modify it because I get errors.

Sub RangeToString()

    Dim myRange As Range
    
       Set myRange = Application.InputBox("LIST 1:" & vbCrLf & "Select Range", Type:=8)
    
    MsgBox myRange

End Sub

Open in new window




Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2008

Commented:
Use this code:

Sub RangeToString()

    Dim myRange As Range
   
       Set myRange = Application.InputBox("LIST 1:" & vbCrLf & "Select Range", Type:=8)
   
    MsgBox Join(Application.Transpose(Application.Transpose(myRange.Value)), ", ")

End Sub

Kevin
CERTIFIED EXPERT
Top Expert 2008

Commented:
Handles row and columns:

Sub RangeToString()

    Dim myRange As Range
   
       Set myRange = Application.InputBox("LIST 1:" & vbCrLf & "Select Range", Type:=8)
   
    If myRange.Rows.Count = 1 Then
        MsgBox Join(Application.Transpose(Application.Transpose(myRange.Value)), ", ")
    ElseIf myRange.Columns.Count = 1 Then
        MsgBox Join(Application.Transpose(myRange.Value), ", ")
    Else
        MsgBox "Select a single row or column of cells."
    End If

End Sub

Kevin
CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thanks Zorvek. But when I say the "Result" I mean the result of the choice within the Input Box which is something like:

'[Workbook.xls]Sheet1'!$A$1:$A$4

Open in new window

Author

Commented:
YEAH WONDERFUL

Thank you matey.... Wonderful !!!

Works like a charm !!!
 

THANKSZZZZZzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
CERTIFIED EXPERT
Top Expert 2008

Commented:
Yep. That's what my third post does. The first two were a misunderstanding.

Kevin

Author

Commented:
Yeaahhhh Man Exactly What I needed. I was at work and stack on that issue until you came.

!!!!!!!!!!!!!!!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.