formula autofilled question

Hi guys so I pretty much work with data and reports all day long and have created some macros on my own but this time around Im stumped.

Here is my problem. I have created a report that uses two formulas in say Columns A and Columns B. So I have told the macro to autofill the formula but the problem is sometimes the data on the report can be 200 rows long and sometimes it can be 500 rows long.

Well when I recorded the macro the data on the report was 200 rows long so now everytime I run the macro the formula is autofilled only up to row 200 so that leaves the rest of the rows without the formula.

Is there anyway I can tell excel to autofill the formula for all entries and not up to just row 200?

Any advice would be greatly appreciated.

Hi

Is there a column that will always be able to determine the last row of data?


rylo

Hi there,

You need to assign a variable that picks up the last row from a column that you know will have the most data in it and then use this variable in your code. For example, (assuming you're using Excel 2003 and you're on the relevant tab) if Column A has the most rows in it, you could use the following:

Please Login or Register  to view this content.

If you get stuck post back with the zipped spreadsheet in question.

HTH

Robert

Hi guys thx for the response.

Actually to give you more info the data I work with is always 10 columns long. It other words that never changes. So the data is going to be the same across the 10 columns.

So lets say tomorrow I download this report. If it has 200 rows of data it's going to be 200 rows across the 10 columns

I hope I'm making sense. The reason I need to fix this is that it's a pain to type in the formula in A2 and B2 and then drag the formula all the way down to very last row of data.

Thx for your replies.