Excel VBA - Assign row to variable and manipulate it?

Question

I've been searching how to assign a row to a variable and manipulate cells through the variable but I can't seem to find how to do this.

x = Sheet5.Range("A1").EntireRow
    MsgBox x(1, 1)

The above code will get me the row into 'x', but is there any way that I can change a cells value using the variable 'x'? x(1,1) = "foo" will not work, and since it's not an object I can't access .Value.

Doug Glancy · Accepted Answer · 2014-03-22 04:17:05Z

2

Here's some sample code:

Sub Ranging()
Dim rng As Excel.Range
Dim ws As Excel.Worksheet

Set ws = ActiveSheet
Set rng = ws.Range("A1").EntireRow
    With rng
        Debug.Print .Cells(1).Value
        Debug.Print .Cells(5).Address
        .Cells(43).Value = "SurfN'Turf"
    End With
End Sub

Debug.Print prints to the VBE's Immediate Window (access with Ctrl-G)

answered Mar 22, 2014 at 4:17

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

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

2 Comments

ShrimpCrackers Over a year ago

Thanks. New to Excel VBA so I had no idea about the Set keyword. Within the With/EndWith is there a way to grab the next (or last) blank row?

Doug Glancy Over a year ago

That's a frequent topic on SO. I generally use the first option in this answer: stackoverflow.com/a/15052318/293078, but sometimes the Previous version is necessary. Happy coding!

Collectives™ on Stack Overflow

Excel VBA - Assign row to variable and manipulate it?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related