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

[Max]

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.

 

[Tom Ogilvy]

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

 

[Max]

Many thanks, Tom. Runs good.

If I want the sub to run automatically upon completion of recalc -- upon
each press of the F9 key to recalc the book (book is set to manual calc
mode) -- how could it be modified?

Thanks

 

[Tom Ogilvy]

Call it from the calculate event, but probably want to turn off events before
you call it and then turn them back on.

http://www.cpearson.com/excel/events.htm if you are not familiar with them.


Sub Doit()
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.Count(Range("G2:H20")) = 0 Then
Range("G2").Value = "No Results to Sort"
End If
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Max]

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