Keeping blank rows when sorting

[duffer_devon]

I have a spreadsheet that I want to keep the blank rows that seperate the data for asthetic reasons. Can I sort just the rows with data in them and still keep the blank rows where they are?

Thanks for your assistance

[EdMac]

I presume you have groups of data separated by blanks and you want to sort within the sets.

I think you can only do this by sorting each set of rows individually - Excel would not know where to put blanks otherwise

[boostjunkie]

I presume you have groups of data separated by blanks and you want to sort within the sets.

I think you can only do this by sorting each set of rows individually - Excel would not know where to put blanks otherwise

I have an idea, but don't know much about changing the filter settings. So perhaps you know.

Is it possible to have it filter for things in common?

For example, you have 2 variations of inputs, and create a 3rd.
a,
b,
a, b

The idea being that you would make the "blank" row a, b and change the font color to make it appear hidden.

Then when you select either a, or b, it could also recognize either value in the "blank" and leave that in the chart? The challenge would be if there were a lot of variations, then that blank cell would need to contain all of them.

[EdMac]

Difficult to visualise what you want to achieve - can you post an example? You need to ZIP it first

[boostjunkie]

Here is a model of what I mean for the filter allowing the "blank" rows. Maybe it's better to refer to them as the universal value row?

[EdMac]

I don't think that what you want is possible (I'm not sure what purpose it serves either).

Perhaps if you were to describe the problem you want to solve, there might be another way to achieve it.

[duffer_devon]

Here is the worksheet I need to sort and keep the blank rows where they are. Any suggestions on how to do this better?
Keep i nmind this is to display golf scores as they are coming in so it will be updated throughout the day.

[martindwilson]

the simplest way is to reference cells on another sheet
do your sorting on the other sheet.

see sheet 2

[Aquasky]

I had an idea and I think I have the code that works. I created a series number column and used Ctrl down to find where the data ranges and blank lines were, then numbered each set as a series. It let me sort within the data sets and keep the blank lines exactly where they were.

Sub SortBySeries()
Dim FinalRow As Long
Dim i As Long
Dim RangeEnd As Long
Dim RangeStart As Long
Dim BlankSwitch As Boolean

'determines last row of data in the desired range
Range("a1048576").Select
Selection.End(xlUp).Select
FinalRow = Selection.Row
Range("a:a").Insert shift:=xlToRight
Range("b2").Select
i = 2
Range("a1").Value = 1
BlankSwitch = False

'creates a column with the series number for each set of data
Do While ActiveCell.Row < FinalRow
If BlankSwitch = False Then
'tests for single line of data in the range, which will mess up the system
If ActiveCell.Offset(1, 0).Value <> 0 Then
RangeStart = Selection.Row
Selection.End(xlDown).Select
RangeEnd = Selection.Row
BlankSwitch = True
Else
'numbering in filled range
RangeStart = Selection.Row
RangeEnd = Selection.Row
BlankSwitch = True
End If
Else
'numbering in range with no data
RangeStart = Selection.Row + 1
Selection.End(xlDown).Select
RangeEnd = Selection.Row - 1
BlankSwitch = False
End If

Range("a" & RangeStart, "a" & RangeEnd).Value = i
i = i + 1
Loop

'sorts by series number to preserve blank rows and related data, then by 2 other criteria
Cells.Select
    ActiveWorkbook.Worksheets(1).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(1).Sort.SortFields.Add Key:=Range("A:A"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets(1).Sort.SortFields.Add Key:=Range("E:E"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets(1).Sort.SortFields.Add Key:=Range("H:H"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(1).Sort
        .SetRange Range("A1:P" & FinalRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

[andriano]

Hello everyone,

I wanted to ask something as well i am trying to create a macro that sorts rows based on value of 2 columns, but every time that value changes in either of them to leave an empty row between them.
Is that possible? In simple words i want to separate my rows and sorting them out alphabetically.

Kind regards, Andriano

[Fotis1991]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.