Problems with a table selection

Question

I'm trying to select the contents (the range X11:Xnn) of the table:

                                        KEYWORD2
  X11            X12          ...          X1N
  X21            X22          ...          X2N
  ...            ...          ...          ...
  XN1            XN2          ...          XNN      
KEYWORD1                

No Important Things…

So, I want select only the range X11:XNN doing a search of the 2 keywords and then select only the Xii.

I'm trying to do this:

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

Selection.Offset(-1, 0).Select 'I don't want the KeyWord1 appears

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

Selection.Offset(1, 0).Select 'I don't want the KeyWord2 appears

Range(??, ??).Select   
End Sub

score 0 · Accepted Answer · edited May 23 '17 at 12:05

Your main issue is that you are not returning the location of the keywords so that you may use them later. Lets capture those results and we can offset them we we call the range.

Sub Macro3()
    Dim rngKeywordOneLocation As Range
    Dim rngKeywordTwoLocation As Range

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

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

    Range(rngKeywordOneLocation.Offset(1, 0), rngKeywordTwoLocation.Offset(-1, 0)).Select

End Sub

Also, you should be careful with your use of .select.

Problems with a table selection

1 Answers1