Need function to count rows until trigger, use variable to specify cell range to copy

**Iblis** · 11-14-2010, 09:18 PM

I'm attempting to do two things in an Excel workbook, which I will believe require the use of at least one, if not two macros, but I'm clueless as to how to go about this.

The first step is creating some sort of macro to count cells in one column. The count would always begin at the same cell (A17) of a sheet that will always be titled 'Segments'. The count should begin with a stored variable of 1, and add 1 for every subsequent cell in the column it checks that does not include the words 'Total Revenues'. When it reaches these words, the count should not add 1, and cease counting, saving the variable.

The second step is using the number that the counting macro reached to output the content of the rows 17 to (17+n) into a separate sheet in the workbook. Similarly, I would like to be able to do this not just for column A, but for any number of columns. For example, rows 17 to 17+n for columns A, B, C and D will be copied to another sheet, becoming rows 7 to 7+n, for the same columns.

Overall, the function will:

a) Determine how many rows in Column A contain relevant data
b) Export the data from several columns on the sheet to another parallel sheet, with the correct number of rows

Strictly speaking, this operation could be done without the first step, if I was aware of how to do the second step, but have the number 'n', calculated by the macro, be replaced with a manually input number in a 'variable' cell.

Either way, I'm clueless as to how to do this. Can anyone help? It would be much appreciated.

Thanks in advance!

**venkat1926** · 11-14-2010, 11:08 PM

QUOTE
some sort of macro to count cells in one column
UNQUOTE
you need not require a macro. you can use countif function.

e.g.suppose the date is from A1 to A30 and you need to start counting only from A17 then the formula will be
=countif(A17:A30,"Total Revenues")
if it is more than 3 you "save the variable" what is meant by saving the variable

second problem not clear. do you want all the rows between 17 and 30(above example) are to be copied or only those rows where "total revenues" are there. clarify.

**Iblis** · 11-15-2010, 12:42 AM

The second problem, to clarify, is that I want the rows counted, from A17 until the cell containing 'Total Revenues' to be copied into an equal number of rows on another sheet. The count function would allow Excel to determine how many rows there are to be copied, so it could identify the proper range of cells to copy.

**royUK** · 11-15-2010, 01:53 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.