VBA Hide specific number of rows based on cell value

RobV1989

New Member
Joined
Sep 5, 2014
Messages
11
Hi all,

I have been searching for a macro which helps to do the following:
I have a financial statement containing 100 rows, where users can fill in invoice numbers and corresponding amounts of dollars. It's a simple sheet, containing a few headers and a lot of rows.

Now, to keep the sheet neat, I would like to have an option if a user gives in the total number of invoices (e.g. in cell A1), for example 26, that the macro hides all rows after 26 until 100. Is this possible? I have been searching the web for quite a few hours but have not succeeded yet.

Thanks in advance,

Rob
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try

Code:
Sub test()
Dim LR As Long, iRows As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
iRows = InputBox("How many rows")
Rows("1:" & LR).Hidden = False
Rows(iRows + 1 & ":" & LR).Hidden = True
End Sub
 
Upvote 0
Try

Code:
Sub test()
Dim LR As Long, iRows As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
iRows = InputBox("How many rows")
Rows("1:" & LR).Hidden = False
Rows(iRows + 1 & ":" & LR).Hidden = True
End Sub

I've tried to get it to work, but since I am quite new to macro's I did not get it to work properly. Could you explain the macro a bit more please?
Also, i've adjusted this:

Code:
iRows = Range("E5")
Rows("6:" & LR.Hidden = False

Since the input is in E5 and it has to hide the rows from the 6th row onwards.
 
Upvote 0
Press ALT + F11 to open the Visual Basic Editor. Select Module from the Inser menu then paste into the white space on the right

Code:
Sub test()
Dim LR As Long, iRows As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
iRows = Range("E5").Value
Rows("6:" & LR).Hidden = False
Rows(iRows + 6 & ":" & LR).Hidden = True
End Sub

Press ALT + Q to close the VBE, press ALT + F8 then in the dialog box that appears double click on test.
 
Upvote 0
Now, Excel does not let me scroll back up again, the screen 'freezes' if i scroll up. Also, when E5=14 for example, all the rows until row 20 (14+6) are hidden.
 
Upvote 0
Apologies, i forgot that my methyod of finding the last row (LR) fails when rows are hidden.Try

Code:
Sub test()
Dim LR As Long, iRows As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
iRows = Range("E5").Value
Rows("6:" & LR).Hidden = False
Rows(iRows + 6 & ":" & LR).Hidden = True
End Sub
 
Upvote 0
Thanks for your help!
It's almost working the way I would like it to work ;)
Could you make a suggestion on how it automatically updates (e.g. if cell E5 is changed, rows are automatically hidden) and final thing is that I would like it to hide until row 100. After that, some informational rows are shown, which should not be hidden.

Once again, thanks for your help.
 
Upvote 0
OK, this code is installed differently - right click the sheet tab, select View Code then paste in

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, iRows As Long
If Target.Address(False, False) = "E5" Then
    LR = 100
    iRows = Target.Value
    Rows("6:" & LR).Hidden = False
    Rows(iRows + 6 & ":" & LR).Hidden = True
End If
End Sub

This will run automatically whenever E5 is changed.
 
Upvote 0

Forum statistics

Threads
1,218,945
Messages
6,145,359
Members
450,611
Latest member
JodiWe

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