I have the following: -BUT it doesn't sort Column R

' determine total range for sorting

Dim lr As Long, lc As Long '(in declarations)

lc = ActiveSheet.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
lr = ActiveSheet.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add _
Key:=Range(Cells(1, 1), Cells(lr, 1)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range(Cells(1, 1), Cells(lr, lc))
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

After this sorts Column R, I then need to copy values of Fields C,D,E for
the rows where value in R = #N/A to another WorkSheet.
Can you help?