How to insert rows depending on value of a reference cell?

Hi Everyone!

I'm looking for a fix (possibly VBA code, or whatever else will work) that will DELETE rows in multiple sheets in multiple areas depending on the value of a reference cell.

My spreadsheet is set up so that someone enters some information regarding a shipment of goods, and then that information is transferred to multiple different sheets to create shipping documents. These documents are then automatically saved as PDF files and sent to myself in an email. The issue is that, depending on the number of "Goods" I'm shipping (the maximum being 6), I would like to have rows in these shipping documents automatically deleted to shorten the length of the document and not create 'blank space' in them. I'm electing for deleting rows (rather than adding) since of course these rows have formulas to carry over the data from the "Input" tab.

Example: If, in the Input tab, it's noted that there are 4 goods being shipped, perhaps I click a button to run the macro/VBA code, which then deletes the unnecessary rows in each of these shipping documents. I've added some photos to make things more clear:

Let's say I have chosen 3 x goods to be shipped. After running the code, it would go into "Document #1" (PHOTO#1) and delete rows 25, 26, & 27, leaving only rows 22, 23, and 24 with the data of the goods being shipped. It would also go into "Document #2" (PHOTO #2) and delete rows 22:24, 30:32, and 38:40. The row placement will always be the same in each of these documents.

I've also attached the workbook itself if that makes it easier to understand. Will greatly appreciate anybody's help with this, and if this turns into being a lot of work I'm happy to pay for it.. $10, $20 or whatever you guys/gals think this is worth. I have gotten a lot of help from this site and I am beginning to feel bad as I have not been able to offer much in return to date. I am doing my very best to learn how to code but it is hard!!

Does it work if you hide the rows?

I created named ranges for all of the hideable areas.
In a module I added a sub to hide the rows based on the input sheet.

A change even in the input sheet runs the sub in the module.

Originally Posted by ByteMarks

Does it work if you hide the rows?

I created named ranges for all of the hideable areas.
In a module I added a sub to hide the rows based on the input sheet.

A change even in the input sheet runs the sub in the module.

Wow this works seamlessly in that spreadsheet! So I tried moving this code over to my active spreadsheet where there are of course more documents. I'm getting an error whenever I change the value of "Good to be shipped" in my active spreadsheet. It should be noted that I'm entering the data on the "Input" tab and the # of Goods cell value is in cell I26, so I changed that range in the code as well.

The error I'm getting is: "Method 'Range' of object'_Global' failed

Which points to this area in the code:

Sub AdjustRows(NumGoods As Integer)
a = Array("CI_HIDE", "PL_HIDE1", "PL_HIDE2", "PL_HIDE3", "SR_HIDE", "SR_HIDE1", "SR_HIDE2", "SR_HIDE3", "SR_HIDE4", "CC_HIDE", "CC_HIDE1", "CC_HIDE2", "CC_HIDE3", "SBOL_HIDE", "AN_HIDE")
For i = 0 To UBound(a)
    With Range(a(i))
        .Rows.Hidden = False
        Select Case NumGoods
            Case 1 To 5
                r = NumGoods + 1
                .Rows(r & ":6").EntireRow.Hidden = True
        End Select
    End With
Next
End Sub

This is what I changed the private sub code to:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
    If .Address = "$I$21" Then
        Select Case .Value
            Case 1 To 6
                Call AdjustRows(.Value)
        End Select
    End If
End With
End Sub

Can you see what has gone wrong here by chance?

Hi, try this - it can add or remove a number of rows that you enter into the B3 cell with the click of a button. Only one thing - the number in cell B1 (updates with each run) should not be deleted.

Insert Rows VBS.xlsm

Use the Name Manager (Ctrl+F3) to check that the named ranges exist and are scoped at workbook level.