Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2006

Tip: Looking for answers? Try searching our database.

Runtime Error 1004 - Help

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Dean - 24 Mar 2006 02:57 GMT
Hi, I am having a little trouble with this code. It produces a runtime
error 1004 in VBA. I think it is coming from this line of code. Would
appreciate any help with this.

Thanks,
Dean

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Sub Macro4()
Dim Lrow As Long
   Dim CalcMode As Long
   Dim StartRow As Long
   Dim EndRow As Long
   Dim rng As Range

   With Application
       CalcMode = .Calculation
       .Calculation = xlCalculationManual
       .ScreenUpdating = False
   End With
   Msg = "Please Check You Have Loaded The Label Paper Correctly - " &
vbCr & "Labels Will Be Sent To Printer - All Data Will Be Deleted " &
vbCr & vbCr & "Click OK When You Are Ready To Proceed"
MsgBox Prompt:=Msg
   With ActiveSheet
       .DisplayPageBreaks = False
       StartRow = 2
       EndRow = 5000
       For Lrow = StartRow To EndRow Step 1
           If IsError(.Cells(Lrow, "B").Value) Then
               'Do nothing, This avoid a error if there is a error in
the cell
           ElseIf .Cells(Lrow, "B").Value = "0" Then
               If rng Is Nothing Then
                   Set rng = .Cells(Lrow, "B")
               Else
                   Set rng = Application.Union(rng, .Cells(Lrow, "B"))
               End If
           End If
       Next
   End With
   'hide all rows in one time
   If Not rng Is Nothing Then rng.EntireRow.Hidden = True

   With Application
       .ScreenUpdating = True
       .Calculation = CalcMode
       Application.Run Macro:="Macro5"
        rng.EntireRow.Hidden = False
       
   End With
End Sub
idyllicabyss@googlemail.com - 24 Mar 2006 03:41 GMT
I don't think rng is populating properly.
I've tried to run this and rng always = 0, never the range that it
should contain.
I don't know why it doesn't work but I worked around it;

Sub Macro4()
Dim Lrow As Long
   Dim CalcMode As Long
   Dim StartRow As Long
   Dim EndRow As Long
   Dim MyRng As Range

   With Application
       CalcMode = .Calculation
       .Calculation = xlCalculationManual
       .ScreenUpdating = False
   End With
   Msg = "Please Check You Have Loaded The Label Paper Correctly - " &
_
vbCr & "Labels Will Be Sent To Printer - All Data Will Be Deleted " & _
vbCr & vbCr & "Click OK When You Are Ready To Proceed"
MsgBox Prompt:=Msg
   With ActiveSheet
       .DisplayPageBreaks = False
       StartRow = 2
       EndRow = 5000
       ZeroCells = 0
       For Lrow = StartRow To EndRow Step 1
           If IsError(.Cells(Lrow, "B").Value) Then
               'Do nothing, This avoid a error if there is a error in
the cell
           ElseIf .Cells(Lrow, "B").Value = "0" Then
                   'Set MyRng = Application.Union(MyRng, .Cells(Lrow,
"B"))
               If ZeroCells > 0 Then
               Application.Union(Selection, .Cells(Lrow, "B")).Select
               Else
               Cells(Lrow, "B").Select
               End If
               ZeroCells = ZeroCells + 1
           End If
       Next
   End With
   'hide all rows in one time
   If Not Selection Is Nothing Then Selection.EntireRow.Hidden = True

   With Application
       .ScreenUpdating = True
       .Calculation = CalcMode
       Application.Run Macro:="Macro5"
        MyRng.EntireRow.Hidden = False

   End With
End Sub

Which seems to work.
Dean - 24 Mar 2006 03:47 GMT
Thanks for your reply however still get a runtime error 1004. Any more
Ideas?

Regards,
Dean

Part of code highlighted is:

Selection.EntireRow.Hidden = True
idyllicabyss@googlemail.com - 24 Mar 2006 03:59 GMT
Sorry, it worked when I did it. Perhaps there is some problem when
copying code from here, I had to put some Underscore _ characters in
where code ran over two or more lines.

Have you considered hiding the rows as you find them?
ElseIf .Cells(Lrow, "B").Value = "0" Then
  rows(Lrow).entirerow.hidden = true

and then when finished
Cells.EntireRow.Hidden = False
kletcho@gmail.com - 24 Mar 2006 07:54 GMT
If you can distinguish the trigger value (right now it is zero) that
indicates when to hide a row with something more distinct you could use
goto special and do something like this:

Sub Test()
   For Each rng In Columns("B:B").SpecialCells(xlCellTypeConstants, 1)
       rng.EntireRow.Hidden = True
   Next rng
End Sub

This one select on cells that have numbers in them in column B and then
hides them.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.