Forum Discussion
Excel vba
I am trying to move rows containing "Click to chat" from an Excel sheet to a new sheet and delete the blank rows.
The code below is giving me an error to debug, and the line below in bold is highlighted. I don't know what to do. Can you assist?
Sub MoveClickToChatRows()
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim lastRow As Long
Dim i As Long
Dim targetRow As Long
Set wsSource = ActiveSheet
Set wsTarget = Sheets.Add
wsTarget.Name = "Click_to_Chat"
lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
targetRow = 1
' Copy header
wsSource.Rows(1).Copy wsTarget.Rows(1)
targetRow = 2
For i = lastRow To 2 Step -1
If InStr(1, wsSource.Rows(i).Text, "Click to chat", vbTextCompare) > 0 Then
wsSource.Rows(i).Copy wsTarget.Rows(targetRow)
wsSource.Rows(i).Delete
targetRow = targetRow + 1
End If
Next i
End Sub