Hide unhide last row with data in a table sequentially using button - VBA Excel

Question

I am looking for a code to hide/Unhide last row with data but in a sequence using button.

I have created a table with a questions in top row and expecting 1 to 10 responses in the rows below. I will hide all 10 rows in a table and will ask responders to use button to unhide one row at a time for their answer.

I have tried following code but it unhide all rows whereas I need to unhide one row upon each click in sequence.

Dim startRow As Long: startRow = 1
Dim lastRow  As Long: lastRow = ActiveSheet.UsedRange.Rows.Count

ActiveSheet.Rows(startRow & ":" & lastRow).Hidden = False

I hope will get some good responses from the community with thanks in advance.

teylyn · Accepted Answer · 2017-01-25 07:30:30Z

0

If you want to unhide one row at a time you can't use a range from startRow to lastRow, right? That will always unhide all rows from start row to last row.

If your intention is to unhide one row with each time the button is clicked, try this macro on the button. It will ignore rows that are already unhidden and unhide the first hidden row starting from the top, then end.

Sub test()
Dim startRow As Long: startRow = 1
Dim lastRow  As Long: lastRow = ActiveSheet.UsedRange.Rows.Count

Do While startRow < lastRow + 1
    If ActiveSheet.Rows(startRow & ":" & startRow).Hidden = True Then
        ActiveSheet.Rows(startRow & ":" & startRow).Hidden = False
        startRow = lastRow + 1
    End If
    startRow = startRow + 1
Loop


End Sub

Another, non-VBA approach would be to keep the rows visible and use formatting to hide the text. Then you can enter a trigger value into a cell that is checked by a conditional formatting rule and the value will be formatted to show. That way you can select any order to unhide answers.

edited Jan 25, 2017 at 7:30

answered Jan 25, 2017 at 7:23

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ExcelMr Over a year ago

Hi Teylyn! It is awesome. This code has worked for me perfectly as I wanted. I am using this code with form button that perform two actions: 1) Open new form for data entry; 2) Unhide new row set to record data upon submission. Actually I have multiple tables and wanted to use this approach to avoid issues with destination cell linkages by adding new row in my file with coding so am using hiding action to deal with it. But thank you so much for your great help!

teylyn Over a year ago

Glad to help. Could you please mark the answer as explained in the Tour, so others can find it more easily.

ExcelMr Over a year ago

Yes done but it shows that people with less than 15 reputation can mark but it is not available publicly. I will visit this answer back again and mark it once I reach to that score. This was my first ever post in this forum. Thanks Again!

teylyn Over a year ago

If you ask the question you can mark the answer. You need reputation only to upvote, not to mark the answer. The two are different. Watch the Tour again.

Collectives™ on Stack Overflow

Hide unhide last row with data in a table sequentially using button - VBA Excel

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related