CODEWITHSUNDEEP
excelvba
Moras
Moras

Reputation: 47

ActiveCell Based Selection

Column A to H has data with some blanks in between. I want to find "ABC" in column A and then select 2 rows above - this will be my ActiveCell.

I want to delete rows in between ActiveCell to Row2 (Active Cell is Dynamic)

Sub format()   
    Cells.Find(What:="abc", _
               After:=ActiveCell, _
               LookIn:=xlFormulas, _
               LookAt:= xlPart, _
               SearchOrder:=xlByRows, _
               SearchDirection:=xlNext, _
               MatchCase:=False, _
               SearchFormat:=False).Activate
    ActiveCell.Offset(-2, 0).Select
    Range(Selection, ActiveCell, A2).Select   
End Sub

Upvotes: 1

Views: 56

Answers (2)

Mikku
Mikku

Reputation: 6654

The code will do the job for you:

Sub format()

Dim rng As Range

Set rng = Cells.Find(What:="abc", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False)

    rng.Offset(-2, 0).Select

    Range(Cells(Selection.Row, 1), Cells(2, 1)).Select
    'Selection.EntireRow.Delete

End Sub

Currently I have commented out the last line which will delete the Rows you want. uncomment it, but first be sure that's what you want to delete.

Upvotes: 1

pnuts
pnuts

Reputation: 59475

For Range please try:

 (ActiveCell, "A2").Select

Upvotes: 0

Related Questions