Macro Error

S

Seeker

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
 
S

Seeker

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 ?
 
S

Seeker

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
 
S

Seeker

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top