ListBox List Print data with sort

[asyamonique]

Dim ws As WorksheetDim rng As Range
Dim i As Long
Dim J As Long


Set ws = Worksheets.Add
Set rng = ws.Range("A1")
For i = 0 To ListBox1.ColumnCount - 1
For J = 0 To ListBox1.ListCount - 1
rng.Offset(J, i) = ListBox1.Column(i, J)
Next J
Next i
Columns("A:A").Resize(, ListBox1.ColumnCount).EntireColumn.AutoFit
Application.DisplayAlerts = False
ws.PageSetup.Orientation = xlLandscape
ws.PrintOut
ws.Delete
Application.DisplayAlerts = True

Good Day,
My question with that given above code is there any possibility to sort listbox column 4 datas from A to Z?
Many Thaks.

 

[ZVI]

Hi,
See if this suits:

Sub Test()
 
  Dim ws As Worksheet
  Dim rng As Range
 
  ' Turn blinking off
  Application.ScreenUpdating = False
 
  ' Provide all actions in new aux workbook
  With Workbooks.Add(xlWBATWorksheet)
   
    Set ws = .Sheets(1)
    Set rng = ws.Range("A1").Resize(ListBox1.ListCount, ListBox1.ColumnCount)
   
    With rng
     
      ' Copy data from ListBox1 to the range
      .Value = ListBox1.List
     
      ' Sort the range by the 4d column
      .Sort .Cells(1, 4), xlAscending, Header:=xlNo
     
      ' Copy the sorted data back to the ListBox1 (if it's required)
      ListBox1.List = .Value
     
    End With
   
    ' Print the sorted data
    ws.PrintOut
   
    ' Close the aux workbook
    .Close False
   
  End With
 
  ' Restore screen updating
  Application.ScreenUpdating = True
 
End Sub

Regards

 

[asyamonique]

Hi,
Thanks for the code it works good, is it possible to autofit the columns?
Or choosing as column number like only the columns 1-3-4-5
Many Thanks

 

[ZVI]

... is it possible to autofit the columns?

To autofit the columns insert this line of the code:
.EntireColumn.AutoFit
before the line:
End With