Eliminate Error Cells, then Move Remaining Data

  • I have a data table comprising labels in column A and data in column B. I have a macro that copies these two columns to columns I & J and then custom sorts them on column J, highest to lowest.


    Often, because of the way the data in col B is obtained, there will be invalid data in the last few cells. This displays as #Ref!. In addition, due to missing data part way down col B, a cell in col B will occasionally display as #N/A. When the macro copies col A & B to col I & J and then sorts it, #N/A appears at the top of col J, followed by #Ref!, and then followed by proper data.


    Range I1:J2 is used as a label. The sorted data + labels start at row 3.


    I need coding that eliminates occurrences of #N/A and/or #Ref! from the top of col J and then moves the remaining contents of col I & J up to row 3. In case it's relevant, when #Ref! occurs in col J the corresponding cell in col I is blank. If #N/A occurs in col J than a label appears in col I; both the label and #N/A can be deleted.


    To illustrate, the data currently on screen shows range I3:J6 as:
    UX #N/A
    #Ref!
    #Ref!
    SR 4.378
    (This isn't posting properly. UX and SR are in col I; #N/A, #Ref! and 4.378 are in col J)


    In this example I3:J5 has to be deleted and then I6:J100 cut & pasted up to row 3.


    I'll be delighted if someone will be kind enough to guide me on setting up the loop to find and delete the instances of #N/A and #Ref!

  • Re: Eliminate Error Cells, then Move Remaining Data


    try this: