Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 1004 - Help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 1004 - Help
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 1004 - Help
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 1004 - Help
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime Error 1004 - Help
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error 1004 | Excel Discussion (Misc queries) | |||
runtime error 1004 | Excel Discussion (Misc queries) | |||
runtime error 1004 | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Runtime error 1004 | Excel Programming |