automatic sort

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 |:>)


On 3/18/05 8:55 PM, in article [email protected],
"Mark" wrote:

> 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

-- [email protected]

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.

Mark wrote:
>
> 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

--

Dave Peterson

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.


CyberTaz wrote:
>
> 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 |:>)
>
> On 3/18/05 8:55 PM, in article [email protected],
> "Mark" wrote:
>
> > 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
>
> -- [email protected]

--

Dave Peterson

On Sat, 19 Mar 2005 05:32:26 -0600, Dave Peterson
<[email protected]> wrote:

>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.
>
Thanks Dave
I'll try this

Mark