How can I print a range variable in the immediate window? Excel VBA

Question

I'm attempting something that should be very simple. However I've just started learning today and and can't quite understand.

This is my code so far:

Public Sub getCellData()
   Dim wb As Workbook: Set wb = ThisWorkbook
   Dim ws As Worksheet: Set ws = wb.Sheets(1)
   Dim rng As Range: Set rng = ws.Range(Cells(1, 2), Cells(4, 2))

   Debug.Print rng
End Sub

The data that I'm working with is this:

I keep getting the "Run-time error '13': Type mismatch" I googled the error and I'm still unsure of how to fix this. I want to print the variable rng in the immediate window.

Is it the *values* in the range that you are trying to print? — John Coleman, Nov 29 '17 at 01:41

score 6 · Answer 1 · answered Nov 29 '17 at 01:51

You could write a simple sub for something like this:

Sub PrintRange(R As Range, Optional delim As String = ", ")
    Dim myRow As Range, V As Variant, i As Long
    For Each myRow In R.Rows
        ReDim V(1 To myRow.Cells.Count)
        For i = 1 To myRow.Cells.Count
            V(i) = myRow.Cells(1, i).Value
        Next i
        Debug.Print Join(V, delim)
    Next myRow
End Sub

Then PrintRange rng would work as expected.

score 5 · Accepted Answer · answered Nov 29 '17 at 01:43

Range is an object, not a value. To output the values, you can iterate the Range. Another way is to use the Transpose function on a single row or column and then Join to get a String value of the array of values within the Range.

Sample code:

Public Sub getCellData()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Sheets(1)

    ' you need to prefix Cells with ws. to clarify the reference
    Dim rng As Range: Set rng = ws.Range(ws.Cells(1, 2), ws.Cells(4, 2))

    ' you cannot debug print the object itself
    'Debug.Print rng

    ' iterate the range
    Dim rngCell As Range
    For Each rngCell In rng
        Debug.Print rngCell.Value
    Next rngCell

    ' use the Transpose function for a single row or column
    Dim strData As String
    Dim wsf As WorksheetFunction: Set wsf = Application.WorksheetFunction
    strData = Join(wsf.Transpose(rng.Value), ",")
    Debug.Print strData


End Sub

Note I updated your Set rng = ... to:

Set rng = ws.Range(ws.Cells(1, 2), ws.Cells(4, 2))

And added ws. as a prefix to Cells in order that the references were explicitly defined.

Thank you. That worked for me. I appreciate your help :) – AP1 Nov 29 '17 at 01:58 — AP1, Nov 29 '17 at 01:58

How can I print a range variable in the immediate window? Excel VBA

2 Answers2

Linked