Run Time Error 1004: Application Defined or Object Defined Error

[BEEJAY]

Greetings All:

Trying to rework existing working code.
The original and my attempts are shown below.
Error shows up at "LastRow = Range............................
If I insert a "Dim Range" statement a completely different error shows up.
As you can see, I don't understand this at all.
Please help.

Option Explicit
Sub HoseCarriers_HPTrk_Hide()
' HIDES NON-SELECTED Rows in Specified Range
Application.ScreenUpdating = False
Dim RowNdx As Long
Dim LastRow As Long
' SELECT RANGE OF CELLS TO BE PROCESSED
' A573 thru G621
' Range("A573:G621").Select

' Original Code
' LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

' Try to convert to work on Specified RANGE, Not complete W/Sheet.
LastRow = Range("A573:G621").Cells(Rows.Count, "B").End(xlUp).Row

' Continue with Original Code
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "B").Value = "x" Then
Rows(RowNdx).Hidden = True
End If

Next RowNdx

' Process Complete - Return all "states" to normal
Application.ScreenUpdating = True
End Sub

 

[Bob Phillips]

How about just

LastRow = Cells(Rows.Count, "B").End(xlUp).Row


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[BEEJAY]

Using your suggestion, as follows, it operates on all 1,500 lines of the sheet,
NOT just on the specified range.
Your thoughts?

Private Sub CommandButton1_Click()
' HIDES NON-SELECTED Rows in Specified Range
Dim RowNdx As Long
Dim LastRow As Long
' SELECT RANGE OF CELLS TO BE PROCESSED
Range("A573:G621").Select
LastRow = Cells(Rows.Count, "B").End(xlUp).Row

For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "B").Value = "x" Then
Rows(RowNdx).Hidden = True
End If
Next RowNdx
End Sub

 

[Bob Phillips]

Well my first thought is that there is some data further downin column B, in
which case you could start higher up with

LastRow = Cells(621, "B").End(xlUp).Row

You can also simplify it with

Private Sub CommandButton1_Click()
' HIDES NON-SELECTED Rows in Specified Range
Dim RowNdx As Long
Dim LastRow As Long
LastRow = Cells(621, "B").End(xlUp).Row

For RowNdx = LastRow To 1 Step -1

Rows(RowNdx).Hidden = Cells(RowNdx, "B").Value = "x"
Next RowNdx
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[BEEJAY]

Again, the code is run only on the designated range - not above it, not below
it.

I may be missing the boat altogether trying to use this method.
I use the original code on our price sheets.
Each line has either a permanent "*", or, an "x" in column "B", which turns
into a "*" when the salesman selects that particular item by entering a
required selection in either column E or F.
The original code, when activated reduces the 1500 rows to a few hundred or
so, by hiding all rows that have an "X" in column B. If column B has an "*"
in it, it does NOT get hidden. Then, the code prints the Non-Hidden rows.

Since the price sheets are getting rather large, I'm trying to speed up the
handling of the sheets by having some (manual) method of hiding groups of
rows that are not required, or that are complete already. For example, once a
tool-box is selected, the other size options should not be required anymore.
Therefore, it would be beneficial to hide those extra, not required, rows.
I realize this whole thing should have been set up in a proper relational
type data-base, and it will end up there, once the project works its way up
the urgency list of our IT dept. In the meantime, Excel seemed to be the most
economical way to go, as all the salesmen, etc. have a MS package anyway AND
it has been working well for years now. I'm just trying to (hopefully)
improve the user friendliness by "reducing" the price sheet size.
Sorry for the rambling. I hope this makes things clearer and maybe triggers
some (other?) way of helping me with this challenge.

As always, thanks so much for your help and consideration.............