take every other cell and put in new colume. No blank cells

[tony hill]

take average of two sequencial cells in column. Put results in new column.
Move to next two cells. Do the same. Etc. The new column should have half
as many cells as the first column.

 

[JP]

Here's how I do it (I'm lazy):

Let's say your values are A1:A10.

1. In B1 put =AVERAGE(A1,A2).
2. Highlight B1:B2
3. Hover over the lower right corner of B2 so your pointer turns into
a plus-sign
4. Click and drag down to the end of your data.
5. PasteSpecial>Values to get the actual averages.
(optional) 6. Paste into a new workbook, then run this procedure to
remove the blank rows.

Sub Del_Empty_Rows()

Dim rng As Excel.Range
Dim A As Long
Application.ScreenUpdating = False

If Selection.Rows.count > 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.Rows
End If

With WorksheetFunction
For A = rng.Rows.count To 1 Step -1
If .CountA(rng.Rows(A).EntireRow) = 0 Then
rng.Rows(A).EntireRow.Delete
Next A
End With

Set rng = Nothing
Application.ScreenUpdating = True

End Sub


You can record yourself doing this if you need a macro.


HTH,
JP

 

[Gord Dibben]

JP

After paste special, you could also select the column and F5>Special>Blanks>OK

Edit>Delete>Entire Rows.


Gord Dibben MS Excel MVP

 

[JP]

Good point, thanks Gord. I have the Del_Empty_Rows() macro on a
toolbar so it's just a button click away, but it's good to remember
the non-macro version.

I also read your post about copy and pasting cells as links, same
response

--JP