Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Error
I would like to embed a macro from http://www.rondebruin.nl/print.htm#Print
which would hide empty rows, print and unhide the rows. However, when I run the macro, it saids "Next without For", so I diable the "Next", then it saids "End With without With"? Here is my macro, any suggestion please? Tks Sub Macro2() Sheets("Records").Select Range("A1:U65536").Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("I2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, SortMethod:=xlStroke, DataOption1:= _ xlSortNormal, DataOption2:=xlSortNormal Selection.AutoFilter Field:=3, Criteria1:="<" Dim rw As Long Dim rng As Range Dim cell As Range Application.ScreenUpdating = False Set rng = Sheets("Records").Range("A1:O65536") With rng.Columns(1) For Each cell In rng If Application.WorksheetFunction.CountA(.Parent.Cells (cell.Row, 1).Range("A1:O65536")) = 0 Then .Parent.Rows(cell.Row).Hidden = True Next cell.Parent.PrintOut .EntireRow.Hidden = False End With Application.ScreenUpdating = True ActiveSheet.ShowAllData Range("A1:U65536").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlStroke, DataOption1:=xlSortNormal End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Error
Hi Mike,
Thanks for your prompt reply. After adding the "End If", now debug shows "If" condition has problem. If Application.WorksheetFunction.CountA(.Parent.Cells (cell.Row, 1).Range("A1:O65536")) = 0 Then Any suggestion ? "Mike H" wrote: Hi, Not tested but your short of an 'End if' try this Sub Macro2() Sheets("Records").Select Range("A1:U65536").Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("I2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, SortMethod:=xlStroke, DataOption1:= _ xlSortNormal, DataOption2:=xlSortNormal Selection.AutoFilter Field:=3, Criteria1:="<" Dim rw As Long Dim rng As Range Dim cell As Range Application.ScreenUpdating = False Set rng = Sheets("Records").Range("A1:O65536") With rng.Columns(1) For Each cell In rng If Application.WorksheetFunction.CountA(.Parent.Cells (cell.Row, 1).Range("A1:O65536")) = 0 Then .Parent.Rows(cell.Row).Hidden = True End If Next cell.Parent.PrintOut .EntireRow.Hidden = False End With Application.ScreenUpdating = True ActiveSheet.ShowAllData Range("A1:U65536").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlStroke, DataOption1:=xlSortNormal End Sub "Seeker" wrote: I would like to embed a macro from http://www.rondebruin.nl/print.htm#Print which would hide empty rows, print and unhide the rows. However, when I run the macro, it saids "Next without For", so I diable the "Next", then it saids "End With without With"? Here is my macro, any suggestion please? Tks Sub Macro2() Sheets("Records").Select Range("A1:U65536").Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("I2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, SortMethod:=xlStroke, DataOption1:= _ xlSortNormal, DataOption2:=xlSortNormal Selection.AutoFilter Field:=3, Criteria1:="<" Dim rw As Long Dim rng As Range Dim cell As Range Application.ScreenUpdating = False Set rng = Sheets("Records").Range("A1:O65536") With rng.Columns(1) For Each cell In rng If Application.WorksheetFunction.CountA(.Parent.Cells (cell.Row, 1).Range("A1:O65536")) = 0 Then .Parent.Rows(cell.Row).Hidden = True Next cell.Parent.PrintOut .EntireRow.Hidden = False End With Application.ScreenUpdating = True ActiveSheet.ShowAllData Range("A1:U65536").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlStroke, DataOption1:=xlSortNormal End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Error
Hi Jacob,
Thanks for your prompt reply. After adding the "End If", now debug shows "If" condition has problem. Is this code missing something? As I copied it from the web and I know not much on the VBA coding (I use the macro recorder in building the macro sheet), what is "CountA" and ".Parent.Cells" refering to, should I change to something else to fit my sheet? Tks "Jacob Skaria" wrote: Add an ENDIF as below within the FOR loop For Each cell In rng If Application.WorksheetFunction.CountA(.Parent.Cells _ (cell.Row, 1).Range("A1:O65536")) = 0 Then .Parent.Rows(cell.Row).Hidden = True End If Next If this post helps click Yes --------------- Jacob Skaria "Seeker" wrote: I would like to embed a macro from http://www.rondebruin.nl/print.htm#Print which would hide empty rows, print and unhide the rows. However, when I run the macro, it saids "Next without For", so I diable the "Next", then it saids "End With without With"? Here is my macro, any suggestion please? Tks Sub Macro2() Sheets("Records").Select Range("A1:U65536").Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("I2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, SortMethod:=xlStroke, DataOption1:= _ xlSortNormal, DataOption2:=xlSortNormal Selection.AutoFilter Field:=3, Criteria1:="<" Dim rw As Long Dim rng As Range Dim cell As Range Application.ScreenUpdating = False Set rng = Sheets("Records").Range("A1:O65536") With rng.Columns(1) For Each cell In rng If Application.WorksheetFunction.CountA(.Parent.Cells (cell.Row, 1).Range("A1:O65536")) = 0 Then .Parent.Rows(cell.Row).Hidden = True Next cell.Parent.PrintOut .EntireRow.Hidden = False End With Application.ScreenUpdating = True ActiveSheet.ShowAllData Range("A1:U65536").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlStroke, DataOption1:=xlSortNormal End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Error
Hi Jacob / Mike,
Thanks for your help, please don't bother to sort out my query as I found an effective macro from other topic as blow which prints data without inclusive the blank rows. Thanks again. With ActiveSheet .PageSetup.PrintArea = Intersect(.UsedRange, Range("A:O")).Address End With "Seeker" wrote: Hi Jacob, Thanks for your prompt reply. After adding the "End If", now debug shows "If" condition has problem. Is this code missing something? As I copied it from the web and I know not much on the VBA coding (I use the macro recorder in building the macro sheet), what is "CountA" and ".Parent.Cells" refering to, should I change to something else to fit my sheet? Tks "Jacob Skaria" wrote: Add an ENDIF as below within the FOR loop For Each cell In rng If Application.WorksheetFunction.CountA(.Parent.Cells _ (cell.Row, 1).Range("A1:O65536")) = 0 Then .Parent.Rows(cell.Row).Hidden = True End If Next If this post helps click Yes --------------- Jacob Skaria "Seeker" wrote: I would like to embed a macro from http://www.rondebruin.nl/print.htm#Print which would hide empty rows, print and unhide the rows. However, when I run the macro, it saids "Next without For", so I diable the "Next", then it saids "End With without With"? Here is my macro, any suggestion please? Tks Sub Macro2() Sheets("Records").Select Range("A1:U65536").Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("I2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, SortMethod:=xlStroke, DataOption1:= _ xlSortNormal, DataOption2:=xlSortNormal Selection.AutoFilter Field:=3, Criteria1:="<" Dim rw As Long Dim rng As Range Dim cell As Range Application.ScreenUpdating = False Set rng = Sheets("Records").Range("A1:O65536") With rng.Columns(1) For Each cell In rng If Application.WorksheetFunction.CountA(.Parent.Cells (cell.Row, 1).Range("A1:O65536")) = 0 Then .Parent.Rows(cell.Row).Hidden = True Next cell.Parent.PrintOut .EntireRow.Hidden = False End With Application.ScreenUpdating = True ActiveSheet.ShowAllData Range("A1:U65536").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ SortMethod:=xlStroke, DataOption1:=xlSortNormal End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|