VBA to replace rows based on name.

[liamfrancis2013]

That was one awful and misleading title.

Anyways:

1.png

This image is from an example sheet (sensitive information and all), unfortunately i cant post the sheet for some reason.

Question: When the rows containing 'Puppy' and 'Kitten', are in that order, i want to swap the contents of the rows.

So my sheet will look like this:

2.png

How do i go about this?

Thanks,
Liam

[daffodil11]

Something like:

Sub MicrowaveKitten()

Dim cell As Range

For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
    If cell = "kitten" Then
        If cell.Offset(1) = "puppy" Then
            cell.EntireRow.Cut
            cell.Offset(2).EntireRow.Select
            Selection.Insert Shift:=xlDown
        End If
    End If
Next

End Sub

[PCI]

Not sure to see a difference between the 2 displays but
try next code

Option Explicit

Sub SwapRow()
Const Wd1 As String = "Puppy"
Const Wd2 As String = "Kitten"
Const NbCol As Integer = 4
Dim I   As Integer
Dim Temp1, Temp2
   For I = 1 To Cells(Rows.Count, 1).End(3).Row - 1
      If ((Cells(I, 1) = Wd1) And (Cells(I, 1) = Wd1)) Then
         Temp1 = Cells(I, 1).Resize(1, NbCol): Temp2 = Cells(I + 1, 1).Resize(1, NbCol)
         Cells(I, 1).Resize(1, NbCol) = Temp2: Cells(I + 1, 1).Resize(1, NbCol) = Temp1
      End If
   Next
End Sub

[liamfrancis2013]

Thanks guys, 'MicrowaveKitten' was all i ended up needing XD