Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting with blank rows in the sort area (Excel 2000 SR1)

    Hello,

    I have a sheet that contains a list which is pulled (using formulas) from a database. The list has some blank lines at the bottom which appear blank. They contain a formula which looks at criteria in the database and as soon as that criteria is met, a record would appear there. It was my way of making the addition of new records in the database, automatically appear in my list.

    The list needs to be sorted by a column containing a meter number. My problem is that I want the blank lines at the bottom of the sort AND I want the numbers ascending (1,2,3,4 etc). Excel either sorts the blank rows to the top or sorts the numbers descending (4,3,2,1 etc).

    Is there any way I can get this to sort the way I want and still have my formulas in the "blank" rows.

    Thanks in advance,

    Christa

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting with blank rows in the sort area (Excel 2000 SR1)

    Can you just sort the fields with the numbers in them and leave the blank ones out of the sort range?

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting with blank rows in the sort area (Excel 2000 SR1)

    Yes, I could do that...but I wanted to make the sort part of a macro. The reason I'm including the blank lines is that at some point in the future...they may not be blank (when a record gets added to the database, it would show up in one of the now blank lines).

    The other thing I though of doing was having the sort range look at only the fields that are not blank and building that into the macro (having the macro redefine the sort range everytime it runs) ...but I don't know how to do that either <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks for your reply...any further help would be much appreciated.

    Christa

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting with blank rows in the sort area (Excel 2000 SR1)

    Could you include a number in the otherwise blank rows that will exceed the highest possible meter number used,e.g. 999999, and replace that with a valid number as each row is populated with data.

    Andrew C

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting with blank rows in the sort area (Excel 2000 SR1)

    THANK YOU!!...that was a great idea...I added a "Z" to my IF statement in that cell and now all the blank lines sort to the bottom of my list and my formula is still in tact.

    Thanks again,

    Christa

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting with blank rows in the sort area (Excel 2000 SR1)

    OK, this may not help much but here goes...I copied this from one of my sheets where I had a similar problem. I have not changed anything so it might take a little to work through it.

    Essentially, it defines a range name in the sheet Journal which expands and contracts, leaving out the blank cells. You might be able to modify it somewhat to fit your needs.

    I guess you might want to sort the entire range, run the code (after you modify it), and then sort the range.

    <pre>Private Sub Worksheet_Activate()
    Application.EnableCancelKey = xlDisabled
    Dim cell As Range
    Dim VL As Range
    b = 263
    Set VL = ThisWorkbook.Sheets("Journal").Range("A263:A277")
    For Each cell In VL
    If cell.Value = "" Then GoTo 10
    b = b + 1
    Next
    End
    10 ThisWorkbook.Unprotect password:=PW
    ThisWorkbook.Names.Add Name:="Vlist", RefersToR1C1:="=Journal!r263c1:r" & b - 1 & "c1"
    ThisWorkbook.Protect password:=PW
    ActiveSheet.Unprotect password:=PW
    Application.ScreenUpdating = False
    For Each cell In Range("a260:a1")
    If cell.Value = Empty And cell.Offset(1, 0).Value =
    Empty Then cell.Offset(1#).EntireRow.Hidden = True
    Next
    Application.MoveAfterReturnDirection = xlToRight
    Application.ScreenUpdating = True
    ActiveSheet.Protect password:=PW
    End Sub
    </pre>


    No <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> at my amateur code you gurus!

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting with blank rows in the sort area (Excel 2000 SR1)

    Forget it! Andrew's idea is much better!!!!

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting with blank rows in the sort area (Excel 2000 SR1)

    Are the blanks all at the end of the range? If so, it should be very easy to find the last non-blank row and sort only the non-blank rows. If you show us your Sort code, I can show you how to do that.
    Legare Coleman

  9. #9
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting with blank rows in the sort area (Excel 2000 SR1)

    Thanks for your help, Michael...compared to me you are a VBA pro... I will likely go with Andrew's idea but I do appreciate you taking the time to help me out.

    Thanks again,

    Christa

  10. #10
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting with blank rows in the sort area (Excel 2000 SR1)

    Yes the blanks are all at the bottom on the range...I'm a real "newby" at writing VBA code...so far all I have is the code I recorded:

    Application.Goto Reference:="MeterData"
    Selection.Sort Key1:=Range("F6"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    I thought of using Ctrl+Shift+8 (finding a continuous range) but there are also blank columns in my list so that doesn't work.

    Andrews suggestion of making the numbers in the blank rows 99999 will work but if you have the time and it's possible, I would like to learn how redefine the range using code.

    Thanks for your help.

    Christa

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting with blank rows in the sort area (Excel 2000 SR1)

    Something like the code below should do what you want:

    <pre>Dim lFirstRow As Long, lFirstCol As Long, lLastRow As Long, lLastCol As Long
    lFirstRow = Worksheets("Sheet1").Range("MeterData").Row - 1
    lFirstCol = Worksheets("Sheet1").Range("MeterData").Column - 1
    lLastCol = lFirstCol + Worksheets("Sheet1").Range("MeterData").Columns.Co unt - 1
    lLastRow = Worksheets("Sheet1").Range("A1").Offset(65535, lFirstCol).End(xlUp).Row - 1
    Worksheets("Sheet1").Range(Range("A1").Offset(lFir stRow, lFirstCol), Range("A1").Offset(lLastRow, lLastCol)).Sort _
    Key1:=Range("F6"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    </pre>

    Legare Coleman

  12. #12
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting with blank rows in the sort area (Excel 2000 SR1)

    I tried the code you sent after editing it as follows:

    Dim lFirstRow As Long, lFirstCol As Long, lLastRow As Long, lLastCol As Long
    lFirstRow = Worksheets("MeterReadingSheet").Range("MeterData") .Row - 1
    lFirstCol = Worksheets("MeterReadingSheet").Range("MeterData") .Column - 1
    lLastCol = lFirstCol + Worksheets("MeterReadingSheet").Range("MeterData") .Columns.Count - 1
    lLastRow = Worksheets("MeterReadingSheet").Range("A1").Offset (65535, lFirstCol).End(xlUp).Row - 1
    Worksheets("MeterReadingSheet").Range(Range("A1"). Offset(lFirstRow, lFirstCol), Range("A1").Offset(lLastRow, lLastCol)).Sort _
    Key1:=Range("F6"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Was there something else I needed to change? The offset in the "lLastRow" line?

    As it is right now...it still sorts all the blank lines to the top. I tried redefining the "MeterData" range to include only non-blank lines but that did not make any difference.

    Thanks,

    Christa

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting with blank rows in the sort area (Excel 2000 SR1)

    That looks pretty much like what I sent with "Sheet1" changed to "MeterReadingSheeet". My code looks for the last non-blank cell in the first column of the "MeterData" range. I see that you are sorting on column F. If that is not the first column in the range, and if the first column does not have empty cells and the first column in the MeterData range is A, then the lLastRow= statement will have to be changed to:

    <pre> lLastRow = Worksheets("MeterReadingSheet").Range("A1").Offset (65535, lFirstCol+5).End(xlUp).Row - 1
    </pre>


    The +5 in that statement is the number of columns that must be added to the first column in the range to get the the column where the emmpt cells are located.

    If this does not solve your problem, could you attache a sample of your workbook to a reply so I can look at it?
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •