CODEWITHSUNDEEP
vbaexcel
veezop
veezop

Reputation: 13

Hiding rows based on value of cell for multiple ranges

I've been trying for some time to write code that will hide rows in multiple ranges if the initial cell value for that row is blank. For some context, this spreadsheet is a quote for a customer with all of the products we offer as line items. Obviously, if the customer doesn't order the product, we don't want to show that line on our quote to them. Here's the code I've tried but keep getting "subscript out of range" errors, and even "_global" errors. Any input would be appreciated since my coding skills are rudimentary at best. Thanks!

Sub quickhide()

Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range, r5 As Range, 
MultiRange As Range
    Set r1 = Sheets("Sheet1").Range("A13:A28")
    Set r2 = Sheets("Sheet1").Range("A31:A35")
    Set r3 = Sheets("Sheet1").Range("A41:A48")
    Set r4 = Sheets("Sheet1").Range("A51:A54")
    Set r5 = Sheets("Sheet1").Range("A57:A58")
    Set MultiRange = Union(r1, r2, r3, r4, r5)

        If MultiRange.Value = "" Then
            MultiRange.EntireRow.Hidden = True
        Else
            MultiRange.EntireRow.Hidden = False
        End If
End Sub

Upvotes: 1

Views: 505

Answers (2)

JJB
JJB

Reputation: 53

An alternative that may be more maintenance friendly for you:

Make the data you are working with into a table. Go to Data tab in your green ribbon (in Excel 2016). Import table into Power Query. Filter your first row to kill the blank (or "null") values. Make any other transformations that you like and "Close and Load" to a new worksheet. Format that table and send it to your client.

You can just refresh the table to account for any changes on your data sheet.

Jake

Upvotes: 0

SJR
SJR

Reputation: 23081

Try this

Sub quickhide()

Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range, r5 As Range, MultiRange As Range, r As Range

Set r1 = Sheets("Sheet1").Range("A13:A28")
Set r2 = Sheets("Sheet1").Range("A31:A35")
Set r3 = Sheets("Sheet1").Range("A41:A48")
Set r4 = Sheets("Sheet1").Range("A51:A54")
Set r5 = Sheets("Sheet1").Range("A57:A58")
Set MultiRange = Union(r1, r2, r3, r4, r5)

For Each r In MultiRange
    r.EntireRow.Hidden = (r.Value = vbNullString)
Next r

End Sub

Upvotes: 1

Related Questions