move entire row containing a target value

[JimmyG64]

Hello,

I've been able to find solutions that are close, but not exact I need a macro that will autorun when the spreadsheet ("data") opens and looks for a target value ("no"), then moves (not copy) the rows that contain the target to another sheet ("Has_No").

The code below only looks for the target in column c, not the whole row. I know it's probably a simple solution for most of you but I can't figure it out.

Thanks in advance and please gentle with the new guy.

Sub Has_No()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Data").UsedRange.Rows.Count
J = Worksheets("Has_No").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Has_No").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Data").Range("C1:C" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "No" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Has_No").Range("A" & J + 1)
xRg(K).EntireRow.Delete
If CStr(xRg(K).Value) = "No" Then
K = K - 1
End If
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub

 

[Kamolga]

Try to replace

[COLOR=#333333]Set xRg = Worksheets("Data").Range("C1:C" & I)[/COLOR]

by

set xRg=[COLOR=#333333]Worksheets("Data").Range(cells(1,3),cells(I,[/COLOR][COLOR=#333333]Worksheets("Data").UsedRange.Columns.Count[/COLOR][COLOR=#333333]))[/COLOR]

 

[JimmyG64]

Thank you for responding. It still only moves the row when "No" is in column c. The goal is for it to move the row if "now" appears in any column in that row.