autosort with blank cell not moving eg
1-3-5-2-4- "-represent blank cell" when sorting i want
1-2-3-4-5- leaving blank cells where thy are

If you are sorting in ascending order, the blanks should be moved to the
bottom.
--
Gary''s Student - gsnu200852


"Anthony" wrote:

> autosort with blank cell not moving eg
> 1-3-5-2-4- "-represent blank cell" when sorting i want
> 1-2-3-4-5- leaving blank cells where thy are
>

Gary, that's what he's trying to avoid, I believe.

Anthony, I know of know built in sorting ability, so you'll have to use a
macro version of sorting to do this. It's actually pretty easy if your
example is indicative of what you need...alternating rows with your values
sorted.

First, is that the case? The blanks are every other row?
Second, your original post shows a ROW of values with spaces, but we are
really sorting down a column, correct?

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Gary''s Student" wrote:

> If you are sorting in ascending order, the blanks should be moved to the
> bottom.
> --
> Gary''s Student - gsnu200852
>
>
> "Anthony" wrote:
>
> > autosort with blank cell not moving eg
> > 1-3-5-2-4- "-represent blank cell" when sorting i want
> > 1-2-3-4-5- leaving blank cells where thy are
> >

Here's a stab at a macro that resorts column A starting at A1 with values in
every other cell. It sorts them, then duplicates the values in another column
twice, resorts by the new column but includes column A, effectively tricking
the "spaces" to reappear...then deletes the temp column I added.

============
Sub AlternatingSort()
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:A" & LastRow).Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal
Columns("B:B").Insert

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & LastRow).Copy Range("B1")
Range("A1:A" & LastRow).Copy Cells(LastRow + 1, "B")

Range("A1:B" & LastRow + LastRow).Sort Key1:=Range("B1"),
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal

Columns("B:B").Delete

End Sub
============

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JBeaucaire" wrote:

> Gary, that's what he's trying to avoid, I believe.
>
> Anthony, I know of know built in sorting ability, so you'll have to use a
> macro version of sorting to do this. It's actually pretty easy if your
> example is indicative of what you need...alternating rows with your values
> sorted.
>
> First, is that the case? The blanks are every other row?
> Second, your original post shows a ROW of values with spaces, but we are
> really sorting down a column, correct?
>
> --
> "Actually, I *am* a rocket scientist." -- JB
>
> Your feedback is appreciated, click YES if this post helped you.
>
>
> "Gary''s Student" wrote:
>
> > If you are sorting in ascending order, the blanks should be moved to the
> > bottom.
> > --
> > Gary''s Student - gsnu200852
> >
> >
> > "Anthony" wrote:
> >
> > > autosort with blank cell not moving eg
> > > 1-3-5-2-4- "-represent blank cell" when sorting i want
> > > 1-2-3-4-5- leaving blank cells where thy are
> > >

An interesting approach!
--
Gary''s Student - gsnu200852


"JBeaucaire" wrote:

> Here's a stab at a macro that resorts column A starting at A1 with values in
> every other cell. It sorts them, then duplicates the values in another column
> twice, resorts by the new column but includes column A, effectively tricking
> the "spaces" to reappear...then deletes the temp column I added.
>
> ============
> Sub AlternatingSort()
> Dim LastRow As Long
> LastRow = Range("A" & Rows.Count).End(xlUp).Row
>
> Range("A1:A" & LastRow).Sort Key1:=Range("A1"), Order1:=xlAscending,
> Header:=xlNo, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
> DataOption1:=xlSortNormal
> Columns("B:B").Insert
>
> LastRow = Range("A" & Rows.Count).End(xlUp).Row
> Range("A1:A" & LastRow).Copy Range("B1")
> Range("A1:A" & LastRow).Copy Cells(LastRow + 1, "B")
>
> Range("A1:B" & LastRow + LastRow).Sort Key1:=Range("B1"),
> Order1:=xlAscending, Header:=xlNo, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
> DataOption1:=xlSortNormal
>
> Columns("B:B").Delete
>
> End Sub
> ============
>
> --
> "Actually, I *am* a rocket scientist." -- JB
>
> Your feedback is appreciated, click YES if this post helped you.
>
>
> "JBeaucaire" wrote:
>
> > Gary, that's what he's trying to avoid, I believe.
> >
> > Anthony, I know of know built in sorting ability, so you'll have to use a
> > macro version of sorting to do this. It's actually pretty easy if your
> > example is indicative of what you need...alternating rows with your values
> > sorted.
> >
> > First, is that the case? The blanks are every other row?
> > Second, your original post shows a ROW of values with spaces, but we are
> > really sorting down a column, correct?
> >
> > --
> > "Actually, I *am* a rocket scientist." -- JB
> >
> > Your feedback is appreciated, click YES if this post helped you.
> >
> >
> > "Gary''s Student" wrote:
> >
> > > If you are sorting in ascending order, the blanks should be moved to the
> > > bottom.
> > > --
> > > Gary''s Student - gsnu200852
> > >
> > >
> > > "Anthony" wrote:
> > >
> > > > autosort with blank cell not moving eg
> > > > 1-3-5-2-4- "-represent blank cell" when sorting i want
> > > > 1-2-3-4-5- leaving blank cells where thy are
> > > >

Trying to avoid pesky loops...

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Gary''s Student" wrote:

> An interesting approach!
> --
> Gary''s Student - gsnu200852

Dear Anthony

I understand you want the blank cells to remain where they are. Try the
below in ColA and feedback.

ColA with the below values (row 4 and row7 blank)
10,7,1, ,2,3, ,6,5,9
will be turned to the below(row 4 and row7 blank)
1,2,3, ,5,6, ,7,9,10


Sub SortWOBlanks()
Dim lngRow As Long
Dim lngCount As Long
Dim lngLastRow As Long
Dim varTemp As Variant
Dim arrTemp As Variant

Application.ScreenUpdating = False
lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
varTemp = Range("A1:A" & lngLastRow)
Range("A1:A" & lngLastRow).Sort Key1:=Range("A1")
lngRow = Range("A" & Rows.Count).End(xlUp).Row
arrTemp = Range("A1:A" & lngLastRow)
Range("A1:A" & lngLastRow) = varTemp
For lngRow = 1 To lngLastRow
If Range("A" & lngRow) <> "" Then
lngCount = lngCount + 1
Range("A" & lngRow) = arrTemp(lngCount, 1)
End If
Next
Application.ScreenUpdating = True

End Sub



If this post helps click Yes
---------------
Jacob Skaria


"Anthony" wrote:

> autosort with blank cell not moving eg
> 1-3-5-2-4- "-represent blank cell" when sorting i want
> 1-2-3-4-5- leaving blank cells where thy are
>