Macro to show one row at a time

M

mesteptoe

Board Regular
Joined
Aug 22, 2008
Messages
105
If I have a table with 100 rows say 1 to 100, of which rows 5 to 100 are hidden for instance, is it possible to create a macro such that by calling the macro Row 5 is shown. But then the next time it is called Row 6 will be shown, then Row7, Row 8 etc. etc.

Thank you.

Graham:LOL:
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Try

Code: 
Sub UnhideOne()
Dim i As Long
Application.ScreenUpdating = False
For i = 5 To 100
    If Rows(i).Hidden Then
        Rows(i).Hidden = False
        Exit For
    End If
Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
This will unhide the next hidden row on the sheet. You could change Activesheet.Rows to a Range Value to tailor it further etc depending on your exact needs.

Code: 
Sub UnhideNext()

For Each mRow In ActiveSheet.Rows
    If mRow.Hidden = True Then
        mRow.Hidden = False
        Exit Sub
    End If
Next

End Sub
 
Upvote 0
Hi both

The "If Rows (i) .Hidden Then" statement has a compile error as it is seems to be expecting a "Then" command after the "."

Haven't tried the other formula yet

Graham
 
Upvote 0
mesteptoe said:
Hi both

The "If Rows (i) .Hidden Then" statement has a compile error as it is seems to be expecting a "Then" command after the "."

Haven't tried the other formula yet

Graham
Click to expand...

It worked when I tested it. Did you copy it from here and paste it into a regular module?
 
Upvote 0
You're right it works. I must have copied it wrong. Next problem, if I have a table with 1000 rows say which is divided into sections, e.g. 1 to 100, 101 to 200, 201 to 300,.........901 to 1000. They are all in the same worksheet, one below another.

In each of the sections I have hidden rows, e.g. rows 5 to 100 are hidden, rows 121 to 200 are hidden, rows 209 to 300 are hidden etc., .....rows 909 to 1000 are hidden.

Is it possible to use the unhide macro in each of the separate sections to unhide the next hidden row in each section. I tried inserting the same macro in the next section down but all it does is unhide the next row in the first section each time.

I need each macro to refer to the section it is included within, i.e. the macro needs to be applicable to a range of rows only.

Thanks

Graham
 
Upvote 0
Untested but try this which will loop through each section.

Code: 
Sub UnhideOne()
Dim i As Long
Application.ScreenUpdating = False
For j = 1 To 10
    For i = 5 + (j - 1) * 100 To 100 + (j - 1) * 100
        If Rows(i).Hidden Then
            Rows(i).Hidden = False
            Exit For
        End If
    Next i
Next j
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi ScottR

You state "You could change Activesheet.Rows to a Range Value to tailor it further etc depending on your exact needs."

How do I limit the formula/ macro to a specific range of rows?

Graham
 
Upvote 0
Would something like

Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Row Mod 100 > 4 Then Rows(Target.Row).Hidden = True
Rows(Target.Row + 1).Hidden = False
End Sub

do what you need? double click visible row to hide and show next, rows 1-4, 100-104, etc will not hide.
 
Upvote 0
But I don't want to hide any rows, just show the next in each range of rows. Please refer to my comments above
 
Upvote 0
You must log in or register to reply here.

Similar threads

Data123
  • Question
Spreadsheet Speed Up Ideas?
Replies
5
Views
220
Alex Blakenburg
A
G
  • Question
VBA help - copy cells from specific table to specific table on another sheet.
Replies
0
Views
320
GardianAngelUK
G
Dan Wilson
  • Solved
Help with a Run-Time Error
Replies
4
Views
237
dreid1011
dreid1011
M
  • Question
VBA help required in creating multiple workbooks from one workbook.
Replies
5
Views
311
jolivanes
jolivanes
T
  • Question
Help with complicated formula
Replies
5
Views
179
Todd Kolar
T

Forum statistics

Threads
1,215,417
Messages
6,124,787
Members
449,188
Latest member
Hoffk036

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top