Changing an Insert Row VBA to an Unhide Rows VBA

Michael515

Board Regular
Joined
Jul 10, 2014
Messages
136
Hi Y'all,

In constructing an automated worksheet for a project I am working on, I realized it'd be better for me to unhide rows based on a certain cell value, rather than insert rows (Some concatenate cell referencing gets thrown off otherwise). I have listed my working Insert Rows Function below and was wondering if anyone can translate it into an unhide rows function for a specified number of rows based on a value in a cell. If I have been unclear at all, please feel free to ask follow-ups. I appreciate any and all the help!

Code:
Sub InsertFundRows()
    Dim i As Integer, n As Integer, m As Long
    n = Sheets("Questionnaire").Range("B20").Value
    m = Sheets("Questionnaire").Range("B30").Row
    For i = 1 To n
        Rows(m + 1 * i).Insert
    Next i
End Sub
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows
If what value is in what column?
If found then that row will be unhidden.
 

Michael515

Board Regular
Joined
Jul 10, 2014
Messages
136
If what value is in what column?
If found then that row will be unhidden.

Well I want to write a code that reads, for example, that if the value in cell B20 reads 4, rows B31-B34 will unhide themselves from the sheet.

Is that more clear?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows
Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

This script only runs if the value in Range ("B20") is changed manually.
Not as a result of a formula.



Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B20")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "4" Then Rows("31:34").Hidden = False
End If
End Sub
 
Last edited:

Michael515

Board Regular
Joined
Jul 10, 2014
Messages
136

ADVERTISEMENT

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window



Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B20")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "4" Then Rows("31:34").Hidden = False
End If
End Sub

Is there any way to automate that more, because as it reads now, if "4" then unhide those 4 rows, but that also means I'll have to do one for if 5 these rows, if 6 these rows, etc. Is there any way to use a dim variable, and offset, or both so if 4, these 4 rows beneath cell B30, if 5, the 5 rows beneath cell B30?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows
Sure anything can be done. I just gave you what you asked for.


So is that what you want?

Hide number of rows indicated in "B20"
Below Row 30
 

Michael515

Board Regular
Joined
Jul 10, 2014
Messages
136

ADVERTISEMENT

Sure anything can be done. I just gave you what you asked for.


So is that what you want?

Hide number of rows indicated in "B20"
Below Row 30

I do appreciate your help, thank you again.

Yes I would like a code that unhides a certain number of rows below Row 30 based on the value in B20.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows
If answers to previous post is yes.
Try this:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B20")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As Long
ans = Target.Value
Dim rr As Long
rr = 31
Rows(rr & ":" & ans).Hidden = False
End If
End Sub
 

Michael515

Board Regular
Joined
Jul 10, 2014
Messages
136
Thank you so much! I had to tweak the code a little bit, but you provided exactly what I needed. Here's my tweaked version:

Code:
    If Target.Address = "$B$20" Then
        Dim ans As Long
        ans = Range("B20").Value
        Dim rr As Long
        rr = 31
        Dim rrans As Long
        rrans = 29 + ans
        Rows(rr & ":" & rrans).Hidden = False
    End If

A follow-up: Do you see anything functionally wrong with my code that could pose problems for the future? Also if I change the value in the cell I get a pop up window that reads:

The value you entered is not valid.

A user has restricted values that can be entered into the cell."

This happens if you lower the cell value, aka if you unhid 5 rows but now change the cell value to 4 (to unhide 4 rows). If you increase it, there is no problem.

I would like to add a piece of code that doesn't have this pop-up happen, or the cancel button automatically gets clicked for the user, whichever is easier. I just am, building this for a client, and I want to make sure the client is able to fill out my document with as much ease as possible.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows
I think what your trying to do is piece together bits and pieces of scripts you have written or found some place to do what you want. Without explaining what your wanting in total. Now I don't think that's a good way to do things but who am I.

I don't believe I can help you any more on this and I hope someone else here at Mr. Excel will be able to help you.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,486
Messages
5,596,448
Members
414,066
Latest member
rjoiner3

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