Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and the Yahoo Answers website is now in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
Excel help - how to vary the length of an autofill?
I hope there's an Excel expert around to help me with this.
I have a spreadsheet where I have a recorded macro doing various things. One of them is to copy or autofill a formula to column C.
The problem is, the data might vary day to day between 2000 items or 40,000. Autofilling 40,000 formulas each time is a big waste of resources and takes ages to do.
How can I make the macro find how many rows of data there are in Col A, and autofill just the appropriate number of rows?
3 Answers
- RayLv 710 years agoFavorite Answer
Hi KK,
Use the counta() function to count the number of rows containing data. For this to work, however, you must be sure that there are no rows with formulas that evaluate to an empty value ("").
Source(s): http://excelhints.com/2008/10/16/count-rows-with-d... http://www.contextures.com/xlfunctions04.html - garbo7441Lv 710 years ago
In your macro dim a variable, i.e. LastRow
Initialize LastRow as:
LastRow = Range("A" & Rows.Count).End(xlUp).Row
This will identify the last row in column A containing data.
Then, you can loop through the filled range in column A and set the formula in column C:
For i = i to LastRow
Cells(i,"C").Formula = "your formula"
- ?Lv 710 years ago
Hello Koolkat,
I'm already out on excel ...
If I could help, I would help.
I hope you see a good soul to help you ...
I'll star the question to see if some of my friends can help.
Hugs!
