macro breaking my head

[mundofanny]

Hi

after realizing that I needed a macro
and after realizing that it was just a bit messy for me I asking for help.

I have several folders.
In each folder I have about 25 spreadsheets with numerical data (nothing fancy). In each sheet there are many more columns and they vary with size.

what I was trying to do is

find the average of each column
copy and paste the average to the first cell of each column
and delete the rest.

says:

A1 ==4
A2 ==3
A3 ==2
A4 ==1


find: A5==average of A1:A4

special paste average to A1
delete values of A1:A5

continue to B

(number of column also varies)

do that for all the columns and if possible for all sheets (but I'll be just as happy with one sheet I could do the sheets manually unless you know

thanks fanny

 

[Norie]

Can you clarify exactly what you want to do?

If the steps you describe were followed there'd be no data left, not even the average.

 

[mundofanny]

I want to find the averages of the columns.
The rest of the values are not needed and cramp up my spreadsheet.
so I'm trying to get them all deleted except for the average but because I don't want to look say in C100 and then in G5 I trying to get them in C1 and G1 respectively.

thanks for the observation

 

[mundofanny]

I think I still way off.

I have found a way to find the last cell and place the average in the next one (it would be also the first empty cell)

lastrow = Cells(Rows.Count, 3).End(xlUp).Row
Cells(lastrow + 1, 3).Formula = "=average(C1:C" & lastrow & ")"

I have kinda found a way to delete rows. But I'm still haven't figure out how to combine them both so I can have only the average value in the first cell of each column. It probably sounds odd but manually is very simple:

find the first empty cell at the bottom of a column and place there the average value of the column; cmd x the average value; delete column; cmd v the average value into the first cell of that column; do next column.

It's very tricky and I don't have enough experience with macros.

I'm working only with numerical values that are in decimal point to the --7 power. no headers, no letters. there are a Y number of columns

I ve been unable to find anything similar in the other posts.

any help much appreciated

thanks much

 

[Armando Montes]

Delete from A1 through next to last row.

 

[konew1]

I suggest the you select all the shhets at once. Select row 1:1 and inssert new row. Select cell A1 enter formula =AVERAGE(A2:A50000). change the reference A5000 to enough to cover all of the data in the longest column that you have on any of the sheets. Copy the formula from A1 across the columns.
Copy row 1 and paste special values. Then delete the data below

 

[pushkardey]

Hi

after realizing that I needed a macro
and after realizing that it was just a bit messy for me I asking for help.

I have several folders.
In each folder I have about 25 spreadsheets with numerical data (nothing fancy). In each sheet there are many more columns and they vary with size.

what I was trying to do is

find the average of each column
copy and paste the average to the first cell of each column
and delete the rest.

says:

A1 ==4
A2 ==3
A3 ==2
A4 ==1


find: A5==average of A1:A4

special paste average to A1
delete values of A1:A5

continue to B

(number of column also varies)

do that for all the columns and if possible for all sheets (but I'll be just as happy with one sheet I could do the sheets manually unless you know

thanks fanny

hope this helps...

Sub avgall()

Set w = ThisWorkbook

For lp1 = 1 To w.Sheets.Count
    Set ws = w.Sheets(lp1)
    For lp2 = 1 To Application.WorksheetFunction.CountA(ws.Range("1:1"))
        k = Application.WorksheetFunction.Average(ws.Range(Left(ws.Cells(1, lp2).Address, 2) & ":" & Left(ws.Cells(1, lp2).Address, 2)))
        ws.Cells(1, lp2).EntireColumn.ClearContents
        ws.Cells(1, lp2) = k
    Next lp2
Next lp1

End Sub

Assumptions:
1) there are no blanks between columns
2) there is nothing below the Data
3) there are no blank worksheets


let me know if it worked out for you...
create a backup before you use this...
insert code in a separate module

 

[mundofanny]

pushkardey, good assumptions
in fact there were no blanks cells, nor blank sheets and nothing below the Data.

It appears it works. Actually it worked...just did some verifications. It's pretty awesome.

 

[pushkardey]

you are welcome

 

[Peter_SSs]

Sounds like you have a solution and it may well run fast enough unless you have a lot of sheets and a lot of columns but might I suggest what I believe is a more efficient code.

The code below ..

- Avoids using WorksheetFunction which generally is not the fastest choice unless there are no vba alternatives.

- Does not average the whole column. Not much point averaging 1,000,000+ rows (Excel 2007+) or even 65,000+ rows (Excel 2003-) if only, say, a few hundred are used.

- Does not need to loop through each column in a worksheet separately, it does them all at once.

<font face=Courier New><br><br><br><SPAN style="color:#00007F">Sub</SPAN> Av()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> formulaBase <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "=AVERAGE(R2C:R#C)"<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets<br>        ws.Rows(1).Insert<br>        <SPAN style="color:#00007F">With</SPAN> ws.UsedRange<br>            LR = .Rows.Count + 1<br>            <SPAN style="color:#00007F">With</SPAN> .Offset(-1).Resize(1)<br>                .FormulaR1C1 = Replace(formulaBase, "#", LR)<br>                .Value = .Value<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            .Clear<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>