Macro and new rows
 

I have a worksheet with 43 rows with a name in each. I have made a macro to
put the names in alphabetical order. It works great until I add a row with a
new name in it. I hit the macro button and it alphabetizes only the first 43
rows not any new rows I have added. Is there a way to add new rows to the
macro automatically without making a new macro every time?

Thanks

Macro and new rows
 

Post your code...
--
HTH...

Jim Thomlinson


"Kevin" wrote:

I have a worksheet with 43 rows with a name in each. I have made a macro to
put the names in alphabetical order. It works great until I add a row with a
new name in it. I hit the macro button and it alphabetizes only the first 43
rows not any new rows I have added. Is there a way to add new rows to the
macro automatically without making a new macro every time?

Thanks

Macro and new rows
 

I am quite new to Excel, I am not sure what you mean by post your code, but
here is what I have done to make the Macro.



I have 43 rows with names in column A, and other info in columns b through
P. If I have a new name to add, I add a row and enter the name in column A.

For the Macro I highlighted all the cells clicked on Data then Sort by
column A


"Jim Thomlinson" wrote:

Post your code...
--
HTH...

Jim Thomlinson


"Kevin" wrote:

I have a worksheet with 43 rows with a name in each. I have made a macro to
put the names in alphabetical order. It works great until I add a row with a
new name in it. I hit the macro button and it alphabetizes only the first 43
rows not any new rows I have added. Is there a way to add new rows to the
macro automatically without making a new macro every time?

Thanks

Macro and new rows
 

It sounds like your code sorts that specific range.

You could change your code to look for the lastrow (based on column A????) each
time you click your button that runs the macro:

Option Explicit
sub MySort()
dim LastRow as long
dim RngToSort as range
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
set rngtosort = .range("a1:x" & lastrow)
end with
with rngtosort
.cells.sort key1:=.columns(1), however you sorted, key2, key3, headers etc
end with
end sub

I sorted A1:X(lastrow used in column A).

Kevin wrote:

I have a worksheet with 43 rows with a name in each. I have made a macro to
put the names in alphabetical order. It works great until I add a row with a
new name in it. I hit the macro button and it alphabetizes only the first 43
rows not any new rows I have added. Is there a way to add new rows to the
macro automatically without making a new macro every time?

Thanks

--

Dave Peterson

Macro and new rows
 

On Mar 16, 1:34 pm, Dave Peterson wrote:
It sounds like your code sorts that specific range.

You could change your code to look for the lastrow (based on column A????) each
time you click your button that runs the macro:

Option Explicit
sub MySort()
dim LastRow as long
dim RngToSort as range
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
set rngtosort = .range("a1:x" & lastrow)
end with
with rngtosort
.cells.sort key1:=.columns(1), however you sorted, key2, key3, headers etc
end with
end sub

I sorted A1:X(lastrow used in column A).

Kevin wrote:

I have a worksheet with 43 rows with a name in each. I have made a macro to
put the names in alphabetical order. It works great until I add a row with a
new name in it. I hit the macro button and it alphabetizes only the first 43
rows not any new rows I have added. Is there a way to add new rows to the
macro automatically without making a new macro every time?

Thanks

--

Dave Peterson

To find your 'code', Tools-Macro-Macros then select your macro from
the list and click on 'Edit'. The VB Editor window will pop up and
you can see your code.

Macro and new rows
 

Thanks fir your help Dave, but like I said I am a beginer at this. How do I
get what you said into my worksheet? Here is a copy of my code

Sub Alphabetize()
'
' Alphabetize Macro
' Macro recorded 16/03/2007 by Bree
'

'
ActiveWindow.SmallScroll Down:=6
Range("A3:P43").Select
Range("P43").Activate
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

"Dave Peterson" wrote:

It sounds like your code sorts that specific range.

You could change your code to look for the lastrow (based on column A????) each
time you click your button that runs the macro:

Option Explicit
sub MySort()
dim LastRow as long
dim RngToSort as range
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
set rngtosort = .range("a1:x" & lastrow)
end with
with rngtosort
.cells.sort key1:=.columns(1), however you sorted, key2, key3, headers etc
end with
end sub

I sorted A1:X(lastrow used in column A).

Kevin wrote:

I have a worksheet with 43 rows with a name in each. I have made a macro to
put the names in alphabetical order. It works great until I add a row with a
new name in it. I hit the macro button and it alphabetizes only the first 43
rows not any new rows I have added. Is there a way to add new rows to the
macro automatically without making a new macro every time?

Thanks

--

Dave Peterson

Macro and new rows
 

Can column A be used to determine the last used row?

If not, then change the "A" in this line to what column can be used:
lastrow = .cells(.rows.count,"A").end(xlup).row

And I'm assuming you are sorting rows 4-whatever and have headers in row 3.

Option Explicit
Sub Alphabetize()
dim LastRow as long
dim RngToSort as range
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
set rngtosort = .range("a3:P" & lastrow)
end with
with rngtosort
.cells.sort Key1:=.columns(1), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with
End Sub


Kevin wrote:

Thanks fir your help Dave, but like I said I am a beginer at this. How do I
get what you said into my worksheet? Here is a copy of my code

Sub Alphabetize()
'
' Alphabetize Macro
' Macro recorded 16/03/2007 by Bree
'

'
ActiveWindow.SmallScroll Down:=6
Range("A3:P43").Select
Range("P43").Activate
Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

"Dave Peterson" wrote:

It sounds like your code sorts that specific range.

You could change your code to look for the lastrow (based on column A????) each
time you click your button that runs the macro:

Option Explicit
sub MySort()
dim LastRow as long
dim RngToSort as range
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
set rngtosort = .range("a1:x" & lastrow)
end with
with rngtosort
.cells.sort key1:=.columns(1), however you sorted, key2, key3, headers etc
end with
end sub

I sorted A1:X(lastrow used in column A).

Kevin wrote:

I have a worksheet with 43 rows with a name in each. I have made a macro to
put the names in alphabetical order. It works great until I add a row with a
new name in it. I hit the macro button and it alphabetizes only the first 43
rows not any new rows I have added. Is there a way to add new rows to the
macro automatically without making a new macro every time?

Thanks

--

Dave Peterson


--

Dave Peterson