vba code no longer working after 8 years: did Excel or vba change?: lngRowsVisible

[Ruthanne]

Hi all!
In 2011, the kind & infamous Moderator Jerry Sullivan gave me vba to achieve adding or deleting rows to have a proportionate sized sheet to print after filtering rows (in this post: https://www.mrexcel.com/forum/excel...s-total-excluding-filtered-hidden-rows-2.html) . It worked great & about 3-6 months ago started bugging w/a Microsoft Visual Basic Run-time error '1004': No cells were found. The yellow highlight in the Microsoft Visual Basic for Applications is these two lines:

     lngRowsVisible = .Resize(, 1) _
            .SpecialCells(xlCellTypeVisible).Count

I'm wondering if the term "lngRowsVisible" has changed?

(My Excel version is from Microsoft Office Professional Plus 2016.)

Thanks in advance for any words of wisdom!

 

[Michael M]

lngRowsVisible

is only a variable assigned by the code.
I'd suggest posting ALL the code to hopefully get a better response !

 

[Fluff]

Run-time error '1004': No cells were found

Means that there are no visible cells within the range you are looking at.

 

[Ruthanne]

Thank you Michael M & Fluff.

My data & the Print_Area is in range B2:Z86 and this is the whole macro.

Sub Pad_or_Delete_MaterialRows()
Sheets("5 MATL LIST").Activate
Sheets("5 MATL LIST").Unprotect "PYPID"
    Dim lngRowsVisible As Long, lngRowsDiff As Long
    Dim lngRowPrintBottom As Long, lngRowTest As Long
    Application.ScreenUpdating = False
 
    With Range(ActiveSheet.PageSetup.PrintArea)
        lngRowPrintBottom = .Rows.Count
        lngRowsVisible = .Resize(, 1) _
            .SpecialCells(xlCellTypeVisible).Count
        lngRowsDiff = 32 - lngRowsVisible
        Select Case lngRowsDiff
            Case Is > 0
                 .Cells(.Rows.Count - 5, 1).Resize(lngRowsDiff) _
                         .EntireRow.Insert
            Case Is < 0
               ' Delete visible blank rows beginning at
               ' .Cells(.Rows.Count - 5) and going up until a total of
               ' lngRowsDiff rows have been deleted or Row 73 is reached
                 For lngRowTest = .Cells.Rows.Count - 6 To 73 Step -1
                    If Not .Cells(lngRowTest, 1).EntireRow.Hidden And _
                        isBlankRow(.Cells(lngRowTest, 1)) Then
                        .Cells(lngRowTest, 1).EntireRow.Delete
                        lngRowsDiff = lngRowsDiff + 1
                        If lngRowsDiff = 0 Then Exit For
                    End If
                 Next lngRowTest
        End Select
    End With
       Application.ScreenUpdating = True
    Sheets("5 MATL LIST").Protect "PYPID"
 End Sub

Thank you pros!

 

[Fluff]

Are there are no visible rows in B2:B86 when you run the macro?

 

[Ruthanne]

Nope. All the rows are visible actually.

 

[Fluff]

Sorry that should have said
Are there any visible rows in B2:B86 when you run the macro?

If you get the 1004, no cells were found error, it means that there are no visible cells in the print area.
Double check what the print area on sheet "5 MATL LIST" is.

 

[Ruthanne]

I have found my big, fat, error...and yes, you are correct to ask twice if there are any visible rows in B2:B86 when you run the macro...because there was when I started to run the macro, but the macro called several sub macros that hid rows based on a column# that changed recently when someone (me??!!) added a column the threw off the column# in the sub! Oh thank you so much for what you said...it got me walking through the assigned macro & the Subs it called to figure this out! I'm retiring in a month & did not want to leave this "bug" behind!

 

[Fluff]

Glad you sorted it & thanks for the feedback.

Enjoy your retirement.

 

[Ruthanne]

Uh oh...I was wrong. I ran all the Subs separately & in the order they were called & all of the called Subs were doing as intended & there was no change to column#s….and the code keeps erroring/highlighting yellow those two lines.
I run a macro on another tab on the same file that is almost identical and it works fine/does not error...so I opened an older version of this file before that macro started "bugging" & check the Print_Area and it started at column G vice this version starting at column B and silly me doesn't understand why that mattered but you were right: "Check the Print_Area"! When I changed the print area to start at Column G, the macro works oh so slick & doesn't bug. SUPER THANK YOU!