General discussion

Locked

Excel Spreadsheet Sorting

By dfish ·
Can you sort columns or rows in Excel that have blanks cells within the data, but keep the blank cells intact. In other words, I need the blank cells to stay where they are after the sorting?

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel Spreadsheet Sorting

by DKlippert In reply to Excel Spreadsheet Sorting

First you need to determine that you have a contiguous range,
Choose one cell in the table and touch Ctrl+Shift+*
This will show the current region.
There should be no empty rows or totally empty columns in the table. If, for some reason I won?t question, you have an empty row, add an index column that just numbers the rows.
Blank cell will stay in synch with the records, IF you either select the entire range, or one single cell. If you select a single column, only that column will be sorted.

Collapse -

Excel Spreadsheet Sorting

by dfish In reply to Excel Spreadsheet Sorting

Poster rated this answer

Collapse -

Excel Spreadsheet Sorting

by M.R.Chambers In reply to Excel Spreadsheet Sorting

I wrote this macro about 3 years ago, to work with Excel 97. The reason I had to write this was that I had a number of columns with the same formula in them, but with a variable number of records (imported from a DB). This meant I had to have the formula in all the rows down to about row 100 or something (there is undoubtedly a better way, but I couldn't think of it at the time). When it came to sorting those records, the rows without a record were included in the sort because they had the formula in them and were therefore considered to be used by Excel, and included int he sort, putting a bunch of blank cells at the top of the list. I think this is similar to what you are experiencing.

This macro determines (somehow...as I said, it was 3 years ago and I have forgotten what i did) how many rows are actually being used and only sorts those rows, ignoring the rest.

You will, of course, need to modify it to suit your worksheet, but here it is...actually, here it isn't: it wontfit: see the next response to this question...sorry.

Collapse -

Excel Spreadsheet Sorting

by dfish In reply to Excel Spreadsheet Sorting

Poster rated this answer

Collapse -

Excel Spreadsheet Sorting

by M.R.Chambers In reply to Excel Spreadsheet Sorting

continued....

Sub SortRaceDetails()
'Select all the used rows of the Race Details worksheet only
'(NOT including the rows with extra formulas)
'Select all records and sort into groups by Age Group, Course, Last Name, in
'that order.
'
'activated by Control+Shift+B

Dim ws As Worksheet 'declare ws as a Worksheet Object
Dim intTotalUsed As Integer
Dim intUsed As Integer
Dim intUnUsed As Integer
Dim intCounter As Integer

Sheets("Race Details").Select
Set ws = Application.ActiveSheet 'assign ws to the active worksheet
intTotalUsed = ws.UsedRange.Rows.count 'assign intTotalUsed to the number of
'used rows (including blank rows with 'formulae
intCounter = 5
intUnUsed = 0
Do
If Cells(intCounter, 1) = "" Then 'Calculate the number of blank rows
intUnUsed = intUnUsed + 1 'that contain formulae (intUnUsed)
EndIf
intCounter = intCounter + 1
Loop Until intCounter = intTotalUsed

intUsed = intTotalUsed - intUnUsed - 1 'Subtract the blank rows from the
'intTotalUsed

Rows("5:" & CStr(intUsed)).Select 'Select the cells in the spcified range

'sort the range according to AgeGroup, Course, LastName

Selection.Sort Key1:=Range("E5"), Order1:=xlAscending, Key2:=Range("D5") _
, Order2:=xlAscending, Key3:=Range("B5"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select

End Sub

I hope you can get some use out of this.

M.R.Chambers

Collapse -

Excel Spreadsheet Sorting

by dfish In reply to Excel Spreadsheet Sorting

Poster rated this answer

Collapse -

Excel Spreadsheet Sorting

by dfish In reply to Excel Spreadsheet Sorting

This question was closed by the author

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums