Excel VBA need to remove all rows with the #REF! value

Question

I have the following code that looks for values with the word "Record" in column L and then copies the row to a separate sheet.

I am getting a type-mismatch error on rows where the value #REF! occurs in column L.

What is the best way to amend the following in order to completely delete those rows and then continue the intended function?

N.B. LastRow is of 'Long' variable type

Sheets("Demand").Rows("1").Copy Sheets("Data").Range("A1")

With Sheets("Demand")

LastRow = .Range("A" & .Rows.Count).End(xlUp).Row

pasteRowIndex = 2

 For r = 1 To LastRow
        If LCase(.Cells(r, "L").Value) Like LCase("Record*") Then
          If UBound(Split(.Cells(r, "L"), ",")) > 0 Then
              i = i + 1
              ReDim v(1 To i)
              v(i) = pasteRowIndex
              End If

          Sheets("Demand").Rows(r).Copy Sheets("Data").Rows(pasteRowIndex)
         pasteRowIndex = pasteRowIndex + 1
      End If
 Next r
End With

Kazimierz Jawor · Accepted Answer · 2017-12-11 12:35:41Z

1

First, you need to iterate from last to first row:

For r = LastRow to 1 step -1

    'Next, add if statement inside the loop checking if cell returns error:
    If IsError(.Cells(r, "L").Value) then

        'if so, delete this row:
        .Cells(r, "L").entirerow.delete

    '..... the rest of your code as ElseIf part of conditional statement

    ElseIf LCase(.Cells(r, "L").Value) Like LCase("Record*") Then
      If UBound(Split(.Cells(r, "L"), ",")) > 0 Then
          i = i + 1
          ReDim v(1 To i)
          v(i) = pasteRowIndex
          End If

      Sheets("Demand").Rows(r).Copy Sheets("Data").Rows(pasteRowIndex)
     pasteRowIndex = pasteRowIndex + 1
    End If
 Next r

answered Dec 11, 2017 at 12:35

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SDROB Over a year ago

Thanks this fixed it!

Subodh Tiwari sktneer · Accepted Answer · 2017-12-11 12:38:04Z

0

Like this...

For r = 1 To LastRow
    If Not IsError(.Cells(r, "L").Value) Then
        If LCase(.Cells(r, "L").Value) Like "record*" Then
            If UBound(Split(.Cells(r, "L"), ",")) > 0 Then
                i = i + 1
                ReDim v(1 To i)
                v(i) = pasteRowIndex
            End If

            Sheets("Demand").Rows(r).Copy Sheets("Data").Rows(pasteRowIndex)
            pasteRowIndex = pasteRowIndex + 1
        End If
    End If
Next r

answered Dec 11, 2017 at 12:38

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Collectives™ on Stack Overflow

Excel VBA need to remove all rows with the #REF! value

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related