Sub to copy only result lines within formula range, omit null string lines
 

Within say, E2:F20 are formulas returning results that are always neatly
bunched at the top. Blank: "" lines (null strings) if any, would appear
below result lines within E2:F20.

What I would like to do is for a sub to copy only the result lines within
E2:F20 (omit the null string lines), then paste special as values into an
adjacent 2 col range ie into G2:Hn*, & sort the pasted range by col G,
ascending.
*n may vary from 2 to 20

And if there are zero result lines, ie E2:F20 contains only null strings,
then "No results to sort" will be written in G2. Thanks for insights.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sub to copy only result lines within formula range, omit null stri
 

Sub Doit()
Dim i As Long
Dim r As Range
Range("E2:F20").Copy
Range("G2").PasteSpecial xlValues
For i = 20 To 2 Step -1
Set r = Cells(i, "G")
If Trim(r.Text) = "" Then r.ClearContents
If Trim(r.Offset(0, 1).Text) = "" Then _
r.Offset(0, 1).ClearContents
Next
Range("G2:H20").Sort Key1:=Range("G2"), _
Order1:=xlAscending, Header:=xlNo
If Application.Count(Range("G2:H20")) = 0 Then
Range("G2").Value = "No Results to Sort"
End If
End Sub

--
Regards,
Tom Ogilvy


"Max" wrote:

Within say, E2:F20 are formulas returning results that are always neatly
bunched at the top. Blank: "" lines (null strings) if any, would appear
below result lines within E2:F20.

What I would like to do is for a sub to copy only the result lines within
E2:F20 (omit the null string lines), then paste special as values into an
adjacent 2 col range ie into G2:Hn*, & sort the pasted range by col G,
ascending.
*n may vary from 2 to 20

And if there are zero result lines, ie E2:F20 contains only null strings,
then "No results to sort" will be written in G2. Thanks for insights.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Sub to copy only result lines within formula range, omit null
 

Thanks again, Tom.

Went with this modification of your sub
which works well on the sheet.


Private Sub Worksheet_Calculate()
Dim i As Long
Dim r As Range
On Error GoTo ErrHandler
Application.EnableEvents = False
Range("E2:F20").Copy
Range("G2").PasteSpecial xlValues
For i = 20 To 2 Step -1
Set r = Cells(i, "G")
If Trim(r.Text) = "" Then r.ClearContents
If Trim(r.Offset(0, 1).Text) = "" Then _
r.Offset(0, 1).ClearContents
Next
Range("G2:H20").Sort Key1:=Range("G2"), _
Order1:=xlAscending, Header:=xlNo
If Application.CountA(Range("G2:H20")) = 0 Then
Range("G2").Value = "No Results to Sort"
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---