Solved

Excel VBA Macro to hide/unhide every 3rd, 4th & 5th row

Posted on 2009-04-07
6
1,008 Views
Last Modified: 2012-05-06
Greetings; I need an Excel VBA Macro to hide/unhide every 3rd, 4th & 5th row on a particular workbook. I found a macro that hides every 3rd and 4th row but I haven't been able to figure out how to modify it to include the 5th row.

Thanks in advance for your consideration.
Hides every 3rd and 4th row:
 
Sub hiderows()
For i = Cells(Rows.Count, "a").End(xlUp).Row - 1 To 2 Step -3
Rows(i).Resize(2).Hidden = True
Next i
End Sub

Open in new window

0
Comment
Question by:dartanion_jm
  • 3
  • 3
6 Comments
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 500 total points
ID: 24087415
Try this:
 

Sub hiderows()
Dim i
For i = Cells(Rows.Count, "a").End(xlUp).Row - 1 To 3 Step -5
Rows(i).Resize(3).Hidden = True
Next i
End Sub

Open in new window

0
 

Author Comment

by:dartanion_jm
ID: 24094316
Thanks for the prompt response Rorya.

Unfortunately the script provided unhid the 1st 5 rows; then hid the next 2 rows; unhide the next row then hid the next 3 rows. Still can't figure this script out.  

The original script provided is close...Unhide rows 1 and 2; hide rows 3 and 4.

Just need it to unhide rows 1 and 2; hide rows 3, 4 and 5; unhide row 6; hide rows 7, 8 & 9; unhide row 10; hide rows 11, 12 & 13...and continue on.

Any assistance you can provide is greatly appreciated. Thanks for your efforts.
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 500 total points
ID: 24094414
That code doesn't unhide any rows so I don't get what you are seeing. Do you need the code to specifically unhide rows 1&2, 6&7 etc?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:dartanion_jm
ID: 24097364
Greetings Rorya; Sorry for the confusing  (am) explanation.

What I'm looking for (after rows 1 and 2 are left unhidden)  is for rows 3,4, & 5 hidden; then leave row 6 unhidden; then hide rows 7, 8 & 9 and continue on for the rest spreadsheet.

Unhidden
Unhidden
Hidden
Hidden
Hidden
Unhidden
Hidden
Hidden
Hidden
Unhidden

Thanks again.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 24099031
So not what you asked for originally then? ;)
Try this:
 

Sub hiderows()
Dim i as long, lngLastRow as long
lngLastRow = Cells(Rows.Count, "a").End(xlUp).Row 
For i = 3 to lngLastRow Step 4
Rows(i).Resize(3).Hidden = True
Next i
End Sub

Open in new window

0
 

Author Closing Comment

by:dartanion_jm
ID: 31567508
Matrix (1999) - Morpheus: "You are the ONE, Rorya!"

It works perfectly. Thank You for the prompt responses and your patience.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question