Average data and delete a row-need help
 

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

Average data and delete a row-need help
 

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" wrote in message
...
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

Average data and delete a row-need help
 

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