Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export a chart in a GIF file. Run-time error '1004': Application-defined or object-defined error; | Excel Programming | |||
Run-time error 1004. Application-defined or object-defined error | Excel Programming | |||
Run Time Error 1004: Application-Defined or Object-Defined Error | Excel Programming | |||
Run-time Error 1004: Application-defined or Object-defined Error | Excel Programming | |||
Macro Run-time Error 1004 Application Defined or Object Defined Error | Excel Programming |