ExcelBanter - Macro Error

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Macro Error (https://www.excelbanter.com/excel-programming/427892-macro-error.html)

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

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

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

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