Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
take every other cell and put in new colume. No blank cells
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
take every other cell and put in new colume. No blank cells
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. PasteSpecialValues 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 On Jan 11, 2:57*pm, tony hill <tony wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
take every other cell and put in new colume. No blank cells
JP
After paste special, you could also select the column and F5SpecialBlanksOK EditDeleteEntire Rows. Gord Dibben MS Excel MVP On Fri, 11 Jan 2008 12:31:39 -0800 (PST), JP wrote: 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. PasteSpecialValues 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 On Jan 11, 2:57*pm, tony hill <tony wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
take every other cell and put in new colume. No blank cells
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 On Jan 11, 4:43*pm, Gord Dibben <gorddibbATshawDOTca wrote: JP After paste special, you could also select the column and F5SpecialBlanksOK EditDeleteEntire Rows. Gord Dibben *MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I validate an entire colume instead of a cell | Excel Worksheet Functions | |||
Change from row to colume n colume to row | Excel Programming | |||
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... | Excel Programming | |||
Copy to first Blank cell in Colum C Non blank cells still exist be | Excel Programming | |||
How can I look up max of one colume and display the colume to the. | Excel Worksheet Functions |