Hello All, I average the data in cells B6 to B36 and place average one cell
over in C36. Once this is done, I want to delete row 6. I get an error at
line "selection.Delete shift:=xlUp" of "Object variable or With block
variable not set". Don't understand what is wrong. Below is what I have and
appreciate any help. Thanks.

Dim selection As range
'Dim myobject As range
Dim i As Long
For i = 6 To 100

Cells(65000, 2).End(xlUp).Offset(0, 1).Select
Cells(i, 3).FormulaR1C1 = "=AVERAGE(R[-30]C[-1]:RC[-1])"
Exit For
Next i
Rows("6:6").Select
selection.Delete shift:=xlUp

End Sub

--
John

The problem is that you have a variable named "selection" that is never set
to anything. That's why you get the "not set" error. "Selection" is a
reserved word in Excel/VBA and therefore should not be used as a variable
name. Delete the line

Dim selection As Range

and your code should work.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"John" <(E-Mail Removed)> wrote in message
news:4BD40144-ED2D-4826-AA41-(E-Mail Removed)...
> Hello All, I average the data in cells B6 to B36 and place average one
> cell
> over in C36. Once this is done, I want to delete row 6. I get an error at
> line "selection.Delete shift:=xlUp" of "Object variable or With block
> variable not set". Don't understand what is wrong. Below is what I have
> and
> appreciate any help. Thanks.
>
> Dim selection As range
> 'Dim myobject As range
> Dim i As Long
> For i = 6 To 100
>
> Cells(65000, 2).End(xlUp).Offset(0, 1).Select
> Cells(i, 3).FormulaR1C1 = "=AVERAGE(R[-30]C[-1]:RC[-1])"
> Exit For
> Next i
> Rows("6:6").Select
> selection.Delete shift:=xlUp
>
> End Sub
>
> --
> John

Thanks, that works for me. So simple.
--
John


"John" wrote:

> Hello All, I average the data in cells B6 to B36 and place average one cell
> over in C36. Once this is done, I want to delete row 6. I get an error at
> line "selection.Delete shift:=xlUp" of "Object variable or With block
> variable not set". Don't understand what is wrong. Below is what I have and
> appreciate any help. Thanks.
>
> Dim selection As range
> 'Dim myobject As range
> Dim i As Long
> For i = 6 To 100
>
> Cells(65000, 2).End(xlUp).Offset(0, 1).Select
> Cells(i, 3).FormulaR1C1 = "=AVERAGE(R[-30]C[-1]:RC[-1])"
> Exit For
> Next i
> Rows("6:6").Select
> selection.Delete shift:=xlUp
>
> End Sub
>
> --
> John