Assignment to constant not allowed

Question

I am getting this error in one of my macros, the code is

Dim rdel1 As Range
Dim rdel2 As Range
Set rdel1 = Sheets("Sheet1").Range(A1:B100)
For Each rdel2 In rdel1.Cells
    If rdel2.Value = "No item selected" Then
        rdel2.Offset(1, 0).EntireRow.Delete
        rdel2.EntireRow.Delete
        rdel2.Address = rdel2.Offset(-1, 0)       "Error in this line"
    End If
Next rdel2

I want to change the address of redel2 by offset(-1,0). I know it dosen't look the right way to write it but I am unable to get the right syntax to change it. Can someone help! Please!

It's unclear to me what you are actually doing. Can you explain what you are doing not what your code is doing? Do you want to iterate over a column and if a either column a or column b equals "No item selected" you want to delete the entire row? keep in mind when you are deleting rows/cells you need to [**iterate backwards](http://stackoverflow.com/questions/19687018/what-does-the-to-and-step-mean-in-vba/19687126#19687126)** — , May 13 '14 at 07:13
@mehow Yeah you are right I should have started backwards. The problem that I was facing were there because I was going from top to the bottom. I did the changes now its working fine. So I no more need `rdel2.Address = rdel2.Offset(-1, 0)` — Gajju, May 13 '14 at 10:31
Using a auto-filter is much more efficient than a range loop — brettdj, May 13 '14 at 10:49

score 2 · Accepted Answer · answered May 13 '14 at 07:37

After you execute

rdel2.EntireRow.Delete

rdel2 will be `Nothing' so any attempt to manipulate it will fail.

If it were not Nothing, and referenceing a cell in a row > 1, then

Set rdel2 = rdel2.Offset(-1, 0)

would work.

It's not clear exactly what you want to achieve, but this may get you started

Sub Demo()
    Dim rdel1 As Range
    Dim rdel2 As Range

    Set rdel1 = Sheets("Sheet1").Range("A1:A100")
    Dim rw As Long
    For rw = rdel1.Rows.Count To 1 Step -1
        Set rdel2 = rdel1.Cells(rw, 1)
        If rdel2.Value = "No item selected" Then
            rdel2.Offset(1, 0).EntireRow.Delete
        End If
    Next
End Sub

score 0 · Answer 2 · answered May 13 '14 at 07:25

rdel2 is a range and .Offset(-1,0) returns a range, just do rdel2 = rdel2.Offset(-1, 0) if you want to change rdel2.

Although, in your case, the For Each loop will update rdel2 so this line will be useless (unless you are not showing all your code and there is actually more between the problematic line and the Next rdel2 statement)

Assignment to constant not allowed

2 Answers2