0

My sheet like below details i want to find word "CN-1" & select all rows continually with this word.

22-005538       ERQXR01200  CN-1    2
22-005538       ERQXR01200  CN-1    2
22-005692       ERQXR01134  CN-1    2
22-005692       ERQXR01134  CN-1    2


77-055988   28  BAXR148314AC    PC-1    3
11-141773   28  BAXR148214AE    PC-1    4
45-167899   28  HAXR148214AE    PC-1    4
77-056293   28  BAXR14#218AE    PC-1    4
77-056293   28  BAXR14#238AE    PC-1    4
1076-76290415   25  HAWR14#112GQ    PC-1    2
11-141774   28  BAXR148238AE    PC-1    4
11-141779   28  BAXR148314AC    PC-1    3
RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
Chirag Parikh
  • 1
  • 1
  • The fastest way would be to use Autofilter. Simply filter the column which has `CN-1` and that will give you your rows ;) Give it a try and if you are stuck then simply post the code that you tried and we will take it from there... BTW this link will get you started ;) http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s – Siddharth Rout Nov 23 '12 at 13:58
  • thx sir i want do like this but speed of macro is very slow we can improve speed. – Chirag Parikh Nov 24 '12 at 06:25

1 Answers1

0

Try this code,

Sub temp()
Dim startRa As Range
Dim endRa As Range

Range("A1").Select
Cells.Find(What:="CN-1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
Set startRa = ActiveCell

Do
    Set endRa = ActiveCell
    Cells.Find(What:="CN-1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
Loop While ActiveCell.Row = endRa.Row + 1

Rows(startRa.Row & ":" & endRa.Row).Select
End Sub
missNimje
  • 1