VBA Code to delete unneeded cells not working as intended

[UncleBajubjubs]

Edit: I solved this right after posting. Solution is at the bottom in case you wanted to try to figure it out yourself.

Hello,
My workbook has a sheet which contains eleven pages worth of cells to be printed. Three of these pages are required, but the other eight are only to be printed if they are needed, which is determined by whether a value on another sheet is zero or not. If the cell has a value of zero, then that page is deleted. I use the exact same code for all eight of the pages which may be deleted, but only three of them actually delete when they have a value of zero.

The code is

<code>
Dim Hoist2Test As Integer
Dim Hoist3Test As Integer
Dim Hoist4Test As Integer
Dim Hoist5Test As Integer
Dim Hoist6Test As Integer
Dim Hoist7Test As Integer
Dim Hoist8Test As Integer
Dim Hoist9Test As Integer
Hoist2Test = Range("Hoist2Cap")
Hoist3Test = Range("Hoist3Cap")
Hoist4Test = Range("Hoist4Cap")
Hoist5Test = Range("Hoist5Cap")
Hoist6Test = Range("Hoist6Cap")
Hoist7Test = Range("Hoist7Cap")
Hoist8Test = Range("Hoist8Cap")
Hoist9Test = Range("Hoist9Cap")

If (Hoist2Test = 0) Then
Rows("104:144").Select
Selection.Delete Shift:=xlUp
End If
If (Hoist3Test = 0) Then
Rows("145:185").Select
Selection.Delete Shift:=xlUp
End If
If (Hoist4Test = 0) Then
Rows("239:288").Select
Selection.Delete Shift:=xlUp
End If
If (Hoist5Test = 0) Then
Rows("289:329").Select
Selection.Delete Shift:=xlUp
End If
If (Hoist6Test = 0) Then
Rows("330:370").Select
Selection.Delete Shift:=xlUp
End If
If (Hoist7Test = 0) Then
Rows("371:420").Select
Selection.Delete Shift:=xlUp
End If
If (Hoist8Test = 0) Then
Rows("421:461").Select
Selection.Delete Shift:=xlUp
End If
If (Hoist9Test = 0) Then
Rows("462:502").Select
Selection.Delete Shift:=xlUp
End If
</code>

For some reason, only Hoist2Test, Hoist6Test, and Hoist8Test properly delete the cells they are supposed to, when each value is set to 0. As far as I can see, it's the exact same code for each if statement. Any ideas?
Thanks.


Edit: Solution: Switch the order they run in. When 2 deletes the cells it's supposed to, it moves everything up, so when 3 targets the cells to delete them, it targets page 4. I reversed the order to make it go from 9 to 2, so as to keep everything deleting the proper cells.

 

[ajamess]

An issue I believe is that your rows are set. Let's say Hoist2Test is 0, it will delete those. Then the Hoist3Values will shift up, and they are no longer in rows 145:185.

I would reverse the order of these: Try doing Hoist9Test First, then all the way down Hoist2Test. See if that fixes your problem.

 

[UncleBajubjubs]

Yup, you hit the nail on the head. I knew something was suspicious about only the even numbered ones disappearing, and actually tried that right before I saw your reply. Thanks!

 

[ajamess]

Yup, you hit the nail on the head. I knew something was suspicious about only the even numbered ones disappearing, and actually tried that right before I saw your reply. Thanks!

Glad to hear it! Best of luck to you!

 

[Rick Rothstein]

@UncleBajubjubs,

This is untested (did not want to go through the trouble of setting things up), but I am pretty sure the following code (test it on a copy of your worksheet) will do what the code you posted was intended to do...

[table="width: 500"]
[tr]
	[td]Dim HC As Long, Rws As String, Rngs As Variant
Rngs = Split("104:144 145:185 239:288 289:329 330:370 371:420 421:461 462:502")
For HC = 2 To 9
  If Range("Hoist" & HC & "Cap").Value = 0 Then Rws = Rws & "," & Rngs(HC - 2 + LBound(Rngs))
Next
Range(Mid(Rws, 2)).EntireRow.Delete[/td]
[/tr]
[/table]