Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I average hourly data into daily data? | Excel Discussion (Misc queries) | |||
average 4 data from different sheets | Excel Discussion (Misc queries) | |||
Average when No Data | Excel Worksheet Functions | |||
How do you average if your data contains a 0? | Excel Worksheet Functions |