CODEWITHSUNDEEP
vbaexcelrange
David Van der Vieren
David Van der Vieren

Reputation: 275

VBA Cut insert function not working correctly

I am trying to cut rows out of a range if they contain "CL" in column B and then insert the cut into another worksheet. It is doing it beautifuly but if Column B does not contain "CL" it will insert a blank row into the spread sheet instead of doing nothing. I am unsure why it is inserting blank rows? Here is the code

With Sheets("Data")
    .Select
    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView
    .DisplayPageBreaks = False
    Firstrow = .UsedRange.Cells(1).Row
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    For Lrow = Lastrow To Firstrow Step -1
        With .Cells(Lrow, "B")

            If Not IsError(.Value) Then

                    If .Value = "CL" Then .EntireRow.Cut
                        Sheets("Sheet1").Select
                        Rows("10:10").Select
                        Selection.Insert Shift:=xlDown

               End If

           End With

       Next Lrow

   End With

End Sub

Upvotes: 0

Views: 3652

Answers (1)

Geoff
Geoff

Reputation: 8850

You're doing the EntireRow.Cut only if you hit a CL, but you're always doing the insert (even if you don't find a CL).

Your indenting makes it look at first glance like you're doing the cut, select and insert all conditionally, but in fact you're using a single-line if form. In this form only the part after then through the end-of-line is conditional; subsequent lines are not conditional.

Here's what you have, if I correct your indenting:

With .Cells(Lrow, "B")
    If Not IsError(.Value) Then
        If .Value = "CL" Then .EntireRow.Cut     '<--- this is a single-line if

        Sheets("Sheet1").Select                  '<--- this, and the next two lines, will always run if .Value is not an error value
        Rows("10:10").Select
        Selection.Insert Shift:=xlDown
    End If  
End With

Try a multi-line if instead:

With .Cells(Lrow, "B")
    If Not IsError(.Value) Then
        If .Value = "CL" Then
            .EntireRow.Cut     '<--- everything from here to the "End If" will run when you hit a CL
            Sheets("Sheet1").Select
            Rows("10:10").Select
            Selection.Insert Shift:=xlDown
        End If
    End If
End With

Upvotes: 3

Related Questions