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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

RobV1989

New Member
Joined
Sep 5, 2014
Messages
11
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.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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.
 

RobV1989

New Member
Joined
Sep 5, 2014
Messages
11

ADVERTISEMENT

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.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

RobV1989

New Member
Joined
Sep 5, 2014
Messages
11

ADVERTISEMENT

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.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,923
Members
414,416
Latest member
Nobu

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
Top