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

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

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

Christa

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

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

No at my amateur code you gurus!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Something like the code below should do what you want:

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.Count - 1
    lLastRow = Worksheets("Sheet1").Range("A1").Offset(65535, lFirstCol).End(xlUp).Row - 1
    Worksheets("Sheet1").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

Reply | Quote

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

Reply | Quote

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:

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

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?

Reply | Quote