I'm a VBA noob, need help with VBA script!

chaoscreater · Aug 10th 2021

[xpost][/xpost]

Hi all,

I'm a complete noob when it comes to VBA scripts, I've never used it before and I'm not a programmer at all. I found a VBA script online and I don't know enough to modify it to suit my needs.

The script has 2 functions - HideRows and ShowRows. When you run them, you are asked to specify a range. For example, I can provide the following range:

'Sheet 1'!H7:H24

It will then look at the cells at H7~H24 in Sheet 1 and hide or unhide the row(s), depending on whether the cell value is a 0 or not.

What I'm trying to do is to provide multiple ranges from multiple sheets. E.g:

'Sheet 1'!H7:H24

'Sheet 2'!D5:D19

The problem is that the VBA script seems to only allow one range at a time and I need to modify this to allow multiple ranges.

For reference, I have attached a test Excel spreadsheet for you to play around with.

Code

Sub HideRows()
Dim WorkRng As Range
Dim cell As Range


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
xTitleId = "Hide Rows"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)


For Each cell In WorkRng.Rows
If (WorksheetFunction.CountIf(cell, "<>0") - WorksheetFunction.CountIf(cell, "") = 0) And (WorksheetFunction.CountA(cell) - WorksheetFunction.Count(cell) = 0) Then
cell.EntireRow.Hidden = True
End If
Next cell


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Display More

Code

Sub ShowRows()
Dim rng As Range
Dim WorkRng As Range
Dim xNumber As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
xTitleId = "Show Rows"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
'xNumber = Application.InputBox("Number", xTitleId, "", Type:=1)
For Each rng In WorkRng
rng.EntireRow.Hidden = False
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Display More

**royUK** · Aug 10th 2021

You don't need most of your code, this will do what you want.

Code

Sub ShowHideRows()
Sheet1.Range("H7:H24").EntireRow.Hidden = Not Sheet1.Range("H7:H24").EntireRow.Hidden
Sheet2.Range("D5:D19").EntireRow.Hidden = Not Sheet2.Range("D5:D19").EntireRow.Hidden
End Sub

Read these links to learn where to put the code and how to rub it.

Where to put the code

How to run the code

chaoscreater · Aug 10th 2021

Sorry, but that's not a good solution for me. Your code assumes that the range is not going to change and is static. You've essentially hardcoded the range into the solution. The code I shared allows you to define a range and this can be any range from any sheet. This makes it flexible and is what I need. All I'm looking for here is to add multiple ranges from multiple sheets into the inputbox. At the moment, I can only feed just one range into it.

**KjBox** · Aug 10th 2021

Try this, the Input box must be completed as follows:

Sheet Name & comma & Range & semi-colon & Sheet Name & comma & Range and so on. For example::

data 1,H7:H24;data 2,O9:O26

Code

Sub HideUnhideRows()
    Dim x, sSht As String, r As Range
    
    s = InputBox("Enter Sheet Name & comma & Range & semi-colon & " & _
                    "Sheet Name & comma & Range etc.", "Sheets & Ranges")
    
    If s <> "" Then
        x = Split(s, ";")
        For Each e In x
            sSht = Split(e, ",")(0)
            Application.ScreenUpdating = 0
            With Sheets(sSht)
                For Each rCel In .Range(Split(e, ",")(1))
                    If rCel = 0 Then
                        .Rows(rCel.Row).Hidden = 1
                    Else
                        .Rows(rCel.Row).Hidden = 0
                    End If
                Next
            End With
        Next
    End If
    
End Sub

Display More

chaoscreater · Aug 10th 2021

Thanks, I'll give it a try at work tomorrow.

Just for my learning purposeS, could you explain what your code does PLEASE? I sort of get parts of it. I think you're trying to split the symbols ',' and ';' in order to process the 2 different ranges from the 2 different sheets separately. I kinda get the hidden = 1 and 0, which is to hide or unhide the rows. But I don't quite get the rest and my coding knowledge and skills is just rubbish......

**royUK** · Aug 10th 2021

Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the URL from the address bar in your browser) to the cross-post. We are here to help so help us help you!

Read this to understand why we ask you to do this and read the Forum Rules before posting again.

https://www.excelguru.ca/content.php?184

**KjBox** · Aug 10th 2021

I am attaching your sample workbook with the code so you can see it working. I have added a button to run the code.

Here is the code with comments to help explain what it is doing

Code

Sub HideUnhideRows()
    Dim x, sSht As String, r As Range
    
    s = InputBox("Enter Sheet Name & comma & Range & semi-colon & " & _
    "Sheet Name & comma & Range etc.", "Sheets & Ranges")
    
    If s <> "" Then
    
    ' Load array x with the result of splitting the Input Box entered string by "semi-colon"
    ' Thus each entry in array x is composed of Sheet Name and Range for that sheet,
    ' separated by a comma
        x = Split(s, ";")
        
    ' Loop through all the elements in array x
        For Each e In x
        
        ' Split each element by the comma. Result is an array of Sheet Name & Range
        ' The (0) is the first item in that array (Sheet name)
        ' That gets assigned to the string variable sSht
        ' A variable has to be used, Sheets(Split(e, ",")(0)) does not work,
        ' but Range(Split(e, ",")(1)) does - an Excel quirk!!!
            sSht = Split(e, ",")(0)
            Application.ScreenUpdating = 0
            With Sheets(sSht)
                
            ' loop through each cell in the entered Range for the entered Sheet
                For Each r In .Range(Split(e, ",")(1))
                    
                ' If a value of zero the row of that cell gets hidden,
                ' else the row of that cell gets to be visible (even if it is currently hidden)
                    If r = 0 Then
                        .Rows(r.Row).Hidden = 1
                    Else
                        .Rows(r.Row).Hidden = 0
                    End If
                Next
            End With
        Next
    End If
 
End Sub

Display More

chaoscreater · Aug 10th 2021

Thank you. I tested it and it hides the rows in both sheets just fine. However, it doesn't seem to unhide them when I click on the same button and use the same range?

**KjBox** · Aug 10th 2021

Sorry, my mistake, misunderstood that part of your request.

Change the code to this

Code

Sub HideUnhideRows()
    Dim x, sSht As String, r As Range
    
    s = InputBox("Enter Sheet Name & comma & Range & semi-colon & " & _
    "Sheet Name & comma & Range etc.", "Sheets & Ranges")
    
    If s <> "" Then
    
    ' Load array x with the result of splitting the Input Box entered string by "semi-colon"
    ' Thus each entry in array x is composed of Sheet Name and Range for that sheet,
    ' separated by a comma
        x = Split(s, ";")
        
    ' Loop through all the elements in array x
        For Each e In x
        
        ' Split each element by the comma. Result is an array of Sheet Name & Range address
        ' The (0) is the first item in that array (Sheet name), (1) is the 2nd (Range address)
        ' That gets assigned to the string variable sSht
        ' A variable has to be used, Sheets(Split(e, ",")(0)) does not work,
        ' but Range(Split(e, ",")(1)) does - an Excel quirk!!!
            sSht = Split(e, ",")(0)
            Application.ScreenUpdating = 0
            With Sheets(sSht)
                
            ' loop through each cell in the entered Range for the entered Sheet
                For Each r In .Range(Split(e, ",")(1))
                    
                ' If a value of zero the row of that cell gets hidden,
                ' made visible if hidden
                    If r = 0 Then .Rows(r.Row).Hidden = Not .Rows(r.Row).Hidden
                Next
            End With
        Next
    End If
 
End Sub

Display More

chaoscreater · Aug 11th 2021

Thank you, that is perfect!

**KjBox** · Aug 11th 2021

You're welcome

**royUK** · Aug 11th 2021

chaoscreator

I hope you have read the Forum Rules and the link about cross posting.

I'm a VBA noob, need help with VBA script!

Participate now!

Share