sort vs. print

[Guest]

Using Excel ('cause I'm too cheap to buy Access) to keep list of videos -- trying to create a list to hand-carry around the house, looking for duplicates to donate to local library or to friends

For convenience, I want to
a. print the list on as few sheets of paper as possible -- 2 or 3 columns on a pag
b. be able to sort alphabetically

a. is easy enough -- figure out how many lines print on a page, make 3 columns that "tall
problem -- can't figure out how to alphabetize the 3 columns as a single grou
b. is easy enough -- one column of data, sort by "A
problem -- prints down the left hand side, 3 pages lon

Final problem -- selfish human who wants it all!!!

 

[Bob Phillips]

Here's s imple macro to do the sort for you. Format in columns as you state,
select any cell within the data and run the macro. Then just print

Sub SortDetails()
Dim iCols As Long
Dim cCols As Long
Dim cRows1 As Long
Dim cRows2 As Long

cCols = ActiveCell.CurrentRegion.Columns.Count
For iCols = 2 To cCols
cRows1 = Cells(Rows.Count, "A").End(xlUp).Row
cRows2 = Cells(Rows.Count, iCols).End(xlUp).Row
Range(Cells(1, iCols), Cells(cRows2, iCols)).Copy
Destination:=Cells(cRows1 + 1, "A")
Next iCols

cRows1 = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(1, "A"), Cells(cRows1, "A")).Sort key1:=Range("A1")

cRows2 = cRows1 \ cCols
If cRows2 * 3 <> cRows1 Then
cRows2 = cRows2 + 1
End If

Range(Cells(cRows2 + 1, "A"), Cells(cRows2 * 2, "A")).Copy
Destination:=Cells(1, "B")
Range(Cells(cRows2 * 2 + 1, "A"), Cells(cRows2 * 3, "A")).Copy
Destination:=Cells(1, "C")
Range(Cells(cRows2 + 1, "A"), Cells(cRows1, "A")).ClearContents

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Using Excel ('cause I'm too cheap to buy Access) to keep list of videos --

trying to create a list to hand-carry around the house, looking for
duplicates to donate to local library or to friends.