macro: sort list, then print w/out blank rows

A

as_sass

Hi,

i have been trying to write a macro (see below) that can do th
following:

1. Sort a list based on two columns.
2. Print the list including header row.

I have encountered the following problems:

1. The first column by which I sort is sorted in Ascending order. I a
sorting a selected range (see below). This resulted in those rows i
that range that were left blank to come up first. I want to ignor
those rows.

2. When I let the macro print the range, it prints the whole range
What do I do to have only those rows printed that are non-blank?

Range("A6:F10005").Select
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending
Key2:=Range("E6") _
, Order2:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
_
DataOption1:=xlSortNormal


Any help appreciated!
a
 
L

Lydya

Hi
You could hide the rows that don't have data in column A using the
autofilter and print the visible range:
--
Range("A6:F10005").Sort _
Key1:=Range("A6"), Order1:=xlAscending, _
Key2:=Range("E6"), Order2:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
With Range("A6")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="<>"
.CurrentRegion.PrintOut
.AutoFilter
End With
 
Top