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

[chaoscreater]

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.

FilePizza - Your files, delivered.

Peer-to-peer file transfers in your web browser.

file.pizza

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

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

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA to Hide & Show Rows
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[chaoscreater]

Sorry, I wasn't aware of the cross posting rule.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

That's a bit strange. I was originally going to do that, but I thought that users would need to perform extra click on the link into another website. Some forums are actually against posting links to other forums. I thought I'd make it easier by posting the actual content of my question, so that users can view it directly without being redirected to somewhere else....

 

[Fluff]

You cannot post a question here asking members to help on another site, if you do the thread is likely to be removed. I suggest that you read the link provided in the rules to understand about cross posting.

 

[Fluff]

Please also provide links to all other sites where you have asked this question.

 

[chaoscreater]

You cannot post a question here asking members to help on another site, if you do the thread is likely to be removed. I suggest that you read the link provided in the rules to understand about cross posting.

OK.....? But that's not what I was asking.

It doesn't make sense that you said:

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

So, I can post links to other forums and I expect people to click the link (which redirects them to the other forum) just so they can read my question, then come back to this site and provide an answer. That's kinda stupid isn't it? I can't post a question here, but I can post a link to the same question in another forum? I also don't quite understand what's the big deal about cross-posting. Obviously, I'm doing it to get as much help as I can from various places.

It doesn't make sense to me that you spend all this time and effort "moderating" this forum, by checking for posts in other forums. Instead of focusing on helping someone out, you go and check other sites and is basically limiting how others try to get help for themselves.

Anyway, the issue is resolved.

 

[Fluff]

Please read the link supplied in the rules, that will explain what cross posting is & why all sites ask you to supply links.