Select Rows based on cell value

[kamalm]

Hi, I use this code to select all Rows that contain number 3 based on column F. The code will select the Entire Row. But what I really want is the Rows contain number 3 to be selected only until Column X, not the entire Row. How can I do that ?

Sub SelRows()
Dim ocell As Range
Dim rng As Range


For Each ocell In Range("F:F")


If ocell.Value = "3" Then


If rng Is Nothing Then


Set rng = ocell.EntireRow
Else


Set rng = Union(rng, ocell.EntireRow)
End If
End If
Next


If Not rng Is Nothing Then rng.Select


Set rng = Nothing
Set ocell = Nothing
End Sub

 

[My Aswer Is This]

You said:

Actually after I select all the Rows that contain number 3, I want to cut and paste at AA1.​

Not sure how the script in Post 7 does what you want. But if your happy that's all that counts.

 

[footoo]

Sub SelRows()
Dim rng As Range, r&
Rows(1).Insert
[F:F].AutoFilter Field:=1, Criteria1:="3"
r = Cells(Rows.Count, "F").End(xlUp).Row
If r > 1 Then
    Set rng = Intersect(Range("F2:F" & Cells(Rows.Count, "F").End(xlUp).Row) _
        .SpecialCells(xlCellTypeVisible).EntireRow, [A:X])
[COLOR=#ff0000]    rng.Copy [AA2][/COLOR]
[COLOR=#ff0000]    rng.Delete Shift:=xlUp[/COLOR]
End If
Rows(1).Delete
End Sub