Row insertion and sorting issue

meandean

New Member
Joined
Jul 27, 2007
Messages
10
I have a spreadsheet with data in 4 different sections with a summary section at the top. Data titles are in column A with the data to the right, arranged by month.
I have created macros to sort the data in each section. Occasionally I need to add a new row to each section for a new data set. When I do this, it screws up my sorting macros for the sections below. It will sort the same rows that it sorted before, not taking into account that the data is now one row lower. How do I fix this?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Code like this

Code:
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row

will return the last used row in column A.

If that doesn't help, please post your macro.
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Since you did not post your current macro code it is hard to tell what will work. Your code evidently uses "hard coded" ranges. Using a Named Range that will expand when rows are inserted might work. It depends on your exact data layout. Maybe having your code reference "CurrentRegion" or "UsedRange" will work. Like I mentioned, it is hard to tell without knowing what your data is like.
Please post the code you are using now and explain if your data ranges are separated from other data or adjacent to it.
 

meandean

New Member
Joined
Jul 27, 2007
Messages
10
Ok, I'm not much of a VB'er, but I'm learning. Here is the code for one of the macro's that I am using to sort the data. There are 4 sections of data above where this sort is occurring. When I want to insert a new row, there will be a new row inserted in all sections.


  • Rows("152:174").Select
    ActiveWorkbook.Worksheets("PE").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("PE").Sort.SortFields.Add Key:=Range( _
    "L152:L174"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("PE").Sort
    .SetRange Range("A152:GB174")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

I have also tried to perform the sort by just selecting the cells and not the entire row. I still have the same problem.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
'Rows("152:174").Select ' I think this is unnecessary
ActiveWorkbook.Worksheets("PE").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("PE").Sort.SortFields.Add Key:=Range( _
"L152:L" & Lastrow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("PE").Sort
.SetRange Range("A152:GB" & Lastrow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
 

meandean

New Member
Joined
Jul 27, 2007
Messages
10
I tried that but then it also sorts the "totals" row at the bottom of the section. I tried again with adding a blank row inbetween the data and the totals row but it still sorted them into the mix.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
If the totals row is 1 row below the last row to be sorted use

Code:
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row - 1
 

meandean

New Member
Joined
Jul 27, 2007
Messages
10
Tried to use that in a section in the middle and it totally whacked things out. :) It sorted EVERYTHING below, not stopping at the bottom of that section.
 

meandean

New Member
Joined
Jul 27, 2007
Messages
10
The sheet looks something like this:

________ J_____ F_____ M_____ A..._____ calculation
A_______ 1_____ 0_____8_____10_____ _____19
B_______ 4_____6_____5_____0____________15
.....

Totals___5_____6_____13____10____________ 34

With 4 sections like this one, same row and column names but different data. Then a summary section at the top that totals all 4.
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Are the sections separated from each other? Like a blank row between the ranges?
If you select a cell in one of the ranges and hit Ctrl-Shift-* does it select just that section?
If so we can use "CurrentRegion" to reference a particular range.
 

Forum statistics

Threads
1,181,730
Messages
5,931,718
Members
436,800
Latest member
abowalid98

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top