Solved

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

Posted on 2009-04-07
6
1,007 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

832 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