1

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.

ShrimpCrackers
  • 4,388
  • 17
  • 50
  • 76

1 Answers1

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)

Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • 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? – ShrimpCrackers Mar 22 '14 at 04:54
  • That's a frequent topic on SO. I generally use the first option in this answer: http://stackoverflow.com/a/15052318/293078, but sometimes the `Previous` version is necessary. Happy coding! – Doug Glancy Mar 22 '14 at 16:23