VBA for One Table Row at a Time + Add Data to Table

[ShawnExcel]

Hello! I wrote the code for this about a year ago, and have since forgotten and can no longer find it! I'm sure you will be able to help me.

I have a table, in column A there are questions (row 1-100) and in column B you are supposed to answer the questions.

I want to make a new worksheet that shows only one question at a time, and allows the user to answer, instead of overwhelming them with 100 questions. It should:
- Find the next blank answer cell, and then display the question associated with the answer
- When the user hits "Submit" it should put that answer into the main table next to the answer
- After someone hits submit it should then find the next blank cell, and show the answer [and repeat]

Does anyone know how to do this? If so, that would be great!

Thanks

 

[Luke M]

Hi Shawn,

Will this work? I moved the MATCH function to a separate cell, to show what Question number we're on. Helps user a bit, but more importantly, helps the macro know where to put the info.

 

[ShawnExcel]

Ah yes that worked perfectly. I just re-did the code so I didn't have to add "Blank" to all 100+ unaswered questions.

There is one additional thing - how do I get the code to skip hidden rows? I have 200 rows with 200 questions, of which only 100 are showing at one time. I only would like it to show the questions/answers for the non-hidden rows. Any ideas?

 

[Luke M]

To check for hidden rows via formulas, we can use a trick of the SUBTOTAL function. See attached, which modifies the formula and question table.