Find First blank cell starting from the top

I am trying to write a macro that will insert a function across multiple columns, but I want to put it one row down from the last used row starting from the top. I have data on A1000, so counting from the bottom up will put my formulas on Row 1002. Instead, how do I get it to insert at the first blank row from the top? Basically, I have between 10-300 rows of data, then nothing else except for A1000. If my last full row is Row 8, how can I insert my formula on row 10? Below is my current macro.

Please Login or Register  to view this content.

I think this might be faster and still get the results you're after. It simply starts looking "up" at row 999 instead of the bottom of the workbook. It then inserts the proper formulas into columns D:AM two rows below the last used row. It also limits the Sumproduct formula to just the used data cells in each column (I assumed row 1 has headers, so set it to start in row 2). This vastly improves the speed of the formula insertion/calculations.

Please Login or Register  to view this content.

You're two for two, Paul. I really appreciate it; I didn't know you could stack those Ranges together like that instead of doing it for each one.
Is it possible to then copy that whole range, and re-paste it as a value? I have summary sheet that already takes forever to calculate, and when I add those lines in, it freezes my whole computer.

To copy the row of formulas and paste elsewhere as values, would simply be

Please Login or Register  to view this content.

You'd want to put that into the For/Next loop so it's performed for each worksheet. You'd have to change the reference to cell A5 on the Summary sheet of course, likely by finding the next available row on the Summary sheet each iteration through the loop. If you run into trouble doing that let us know.

You missed the PasteSpecial method, but yes, that's all you need to do in that case.

Please Login or Register  to view this content.

Can you clarify?

How can you make it do what for only one sheet at a time? The entire macro (adding formulas in proper row, formatting & pasting as values) or just the pasting values bit? Upon closing, you want to delete the summary values created from all worksheets (the Dx:AMx ranges)? If you're copying that info to the summary sheet, it would make more sense to include that copy in the code loop, as well as deleting the values afterward from each sheet.

Error in the code?

Please Login or Register  to view this content.

It would probably be easier to create one macro, referencing "ActiveSheet" as you've done, but put it in a standard module. Then in each sheet create a button that simply calls that one macro. Would be a lot more efficient than having that code 42 times.

Sounds right to me.

Glad you've got it sorted out.

Because your code specifically points to column C in every line (and column B in another) when your previous code was looking at columns C and D?

Please Login or Register  to view this content.

In your original code, you checked to see if C3 (column C) of the last row (+2) was blank, and if so you started entering formulas to the right of that - in columns D:AM. Your SUMPRODUCT formula multiplied each columns' value against the value in column C and then added them up.

Your latest code is now checking column B for the blank and starting to put formulas in C:AL. The SUMPRODUCT formula itself, though, is pointing to the original columns. My guess is that if you want to have your formulas in C:AL and sum their products with column AM, then change the D2:D bit to C2:C. So the formula in, say, C20, will be a sumproduct between C2:C18 and AM2:AM18. In D20, the sumproduct would be between D2:D18 and AM2:AM18, and so on.

Try fixing the ":A:" to ":AL" in the Copy line again.