automatic sort

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I have a spreadsheet with columns A:G and unlimited rows.
Is there any way to automatically sort the whole sheet in alphabetical
order by, let say column G?
The problem is that users of this sheet enter data in the rows and I
need it to be sorted as I mentioned above but many of them don't know
much Excel and can't do the sort from the menu so I was thinking maybe
some kind of macro could do it but I don't know how to write it.
I should mention that I already have macro on that sheet that sets up
printing area, also with ctrl+D it deletes the current row and moves
everything up so if I add another one....
I just don't know how to do it so I need some help, please

Mark
 
A macro just to sort on one column is probably more trouble than it's worth,
and there is no need for anyone to use the sort command in the menu to sort
on a single column. All they need do is click in column G (per your example)
& click either the A-Z (Ascending Sort) or Z-A (Descending Sort) button on
the toolbar.

HTH |:>)


I have a spreadsheet with columns A:G and unlimited rows.
Is there any way to automatically sort the whole sheet in alphabetical
order by, let say column G?
The problem is that users of this sheet enter data in the rows and I
need it to be sorted as I mentioned above but many of them don't know
much Excel and can't do the sort from the menu so I was thinking maybe
some kind of macro could do it but I don't know how to write it.
I should mention that I already have macro on that sheet that sets up
printing area, also with ctrl+D it deletes the current row and moves
everything up so if I add another one....
I just don't know how to do it so I need some help, please

Mark
Click to expand...

-- (e-mail address removed)
 
I'd put a button from the forms toolbar on the worksheet and assign this macro
to it.

Option Explicit
Sub sortMyData()

Dim LastRow As Long
Dim myRng As Range

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("a1:G" & LastRow)
myRng.Sort Key1:=.Columns(7), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With

End Sub

I assumed that you can find the lastrow of the range by looking at column A.
Adjust that if necessary.

And I assumed that you had headers in Row 1 and details from row 2 down.
 
I'd be very careful with this. Depending on your selection, excel can guess the
wrong range to sort.

And if, by mistake, the users sort just column G, it'll be difficult to put the
data back the way it should be.
 
I'd put a button from the forms toolbar on the worksheet and assign this macro
to it.

Option Explicit
Sub sortMyData()

Dim LastRow As Long
Dim myRng As Range

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("a1:G" & LastRow)
myRng.Sort Key1:=.Columns(7), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With

End Sub

I assumed that you can find the lastrow of the range by looking at column A.
Adjust that if necessary.

And I assumed that you had headers in Row 1 and details from row 2 down.
Click to expand...
Thanks Dave
I'll try this

Mark
 
Back
Top