Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 247
Default Run Time Error 1004: Application Defined or Object Defined Error

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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10,593
Default Run Time Error 1004: Application Defined or Object Defined Error

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" wrote in message
...
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



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 247
Default Run Time Error 1004: Application Defined or Object Defined Er

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" wrote:

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" wrote in message
...
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




  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10,593
Default Run Time Error 1004: Application Defined or Object Defined Er

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" wrote in message
...
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" wrote:

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" wrote in message
...
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






  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 247
Default Run Time Error 1004: Application Defined or Object Defined Er

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.............

"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Export a chart in a GIF file. Run-time error '1004': Application-defined or object-defined error; [email protected] Excel Programming 4 September 16th 07 11:09 PM
Run-time error 1004. Application-defined or object-defined error Martin Brennan Excel Programming 2 June 7th 07 01:22 PM
Run Time Error 1004: Application-Defined or Object-Defined Error Diana[_7_] Excel Programming 6 April 17th 07 04:21 PM
Run-time Error 1004: Application-defined or Object-defined Error Adrian Excel Programming 6 August 23rd 05 06:28 AM
Macro Run-time Error 1004 Application Defined or Object Defined Error Anddmx Excel Programming 6 June 9th 04 03:40 PM


All times are GMT +1. The time now is 09:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"