Forum Discussion

FKanangila's avatar
FKanangila
Copper Contributor
Feb 10, 2026

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

 

No RepliesBe the first to reply