CODEWITHSUNDEEP
excelvba
Troy Cosentino
Troy Cosentino

Reputation: 4778

how to get a 'Cell' object in excel VBA

So i was using a for each loop to go through a bunch of rows. I ran into a problem where i was deleting rows and it was causing a row to get skipped so i have changed to a do while loop.

The problem i am having is trying to get a 'cell' object. Before when i had:

For Each C In Worksheets("Blah").Range("A2:A" & lastRow).Cells

I could do things like 

C.Offset(1, 0)
C.Value = "Troy"

Etc. I tried using:

C = Worksheets("Blah").Cells(iRow, 2)

but that just gives C the value of the cell. How do i get the actual cell object?

Thanks

Upvotes: 13

Views: 76396

Answers (2)

Валерий Зайцев
Валерий Зайцев

Reputation: 31

To get an object reference and not the value itself you need to use 'Set'

Set C = Worksheets("Blah").Cells(iRow, 2)

Upvotes: 1

Dick Kusleika
Dick Kusleika

Reputation: 33145

When you delete rows, it's always best to go from the bottom up. Your particular problem is that you need the Set keyword to set C to the range object rather than "Let" C to the range Value. Range's have a default property, Value, and when you omit a property that's the property that's used. For that reason, you have to use Set when assigning object variables. Here's an example of looping backward to delete rows and assigning an object variable.

Sub DeleteRows()

    Dim i As Long
    Dim rRng As Range
    Dim rCell As Range

    Set rRng = Sheet1.Range("A1:A9")

    For i = rRng.Rows.Count To 1 Step -1
        Set rCell = rRng.Cells(i, 1)
        If rCell.Value Mod 2 = 1 Then
            rCell.EntireRow.Delete
        End If
    Next i

End Sub

Upvotes: 19

Related Questions