We help IT Professionals succeed at work.

Problems with a macro

Attached is a macro that is suppose to perform the following:
1. Find the "Summary" table in the worksheet (works)
2. Selects the the cell in column K that is 2 rows below the cell containing the "Summary" text (works)
3. If this cell is not blank, then select the cell in column K that is 1 row below the cell containing the "Summary" text (doesn't work)
4. Select all the contents of the table (should work, if step three works)

Hopefully this explains my issue well enough.  If not, I can clarify further.  Thanks for any suggestions
Sub Test()
Dim rFind As Range
    'Application.ScreenUpdating = False
  
    With SheetSummary
        Set rFind = .Cells.Find(what:="Summary", lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
            .Cells(rFind.Row + 2, "K").Select
            
            If ActiveCell.Value <> "" Then
                Set rFind = .Cells.Find(what:="Summary", lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
                If Not rFind Is Nothing Then
                    .Cells(rFind.Row + 1, "K").Select
                    Range(Selection, Selection.End(xlDown)).Select
                    Range(Selection, Selection.End(xlToRight)).Select
                End If
            End If
        End If
        
    End With

    'Application.ScreenUpdating = True
    
End Sub

Open in new window

Comment
Watch Question

SANTABABYSoftware Professional
CERTIFIED EXPERT

Commented:
Do you have a summary table (the first occurence of "summary" in the worksheet) followed by a bunch of rows with "summary" texts?
An example of your worksheet data would clarify this.

If the answer to the above question is NO, do you really need to search again (line #11)?  Removal of Line 11, 12, 13 would use the last tagged cell and offset to that cell(Line #14).

Like I said, an example will help to answer this accurately.

Author

Commented:
Attached is a sample.  If you look at my code, the portion that is commented out does not work.  Maybe my entire approach is wrong.  I'm open to any alternative suggestions.  Thanks!
Question-Sample.xlsm
Are you saying that step 4 is to select the cells between those identified in steps 2 and 3?
But won't that always be two cells? I'm confused about what you are trying to achieve I think.
SANTABABYSoftware Professional
CERTIFIED EXPERT

Commented:
When I uncommented and ran your code, Item1 thru Item 7 are gotselected (i.e. row 15 thru 21) however the columnspan needs to be corrected, it is going beyond the table boundary (unleass that's what you intended).
Is this the behavior you intended?
FYI, I'm running Excel 2010.

Author

Commented:
I think my newly attached worksheet will be much easier to understand.  Perform the following, and you should get a much better visual aid of what I'm trying to accomplish.

First, open the newly attached file.  Click the the [Click "Test2()"] button and all the contents of the table will get selected.  This is what I need, but the the problem is, if the table ever only consists of one row, it will select too many rows below it.  Click the [Click "Test3()"] button and you'll see what I need.

This is the issue I'm trying to correct.  I hope this helps.  Thanks for trying to make sense of it.



Question-Sample-REVISED.xlsm
Try this:
Sub Test2()

Dim rFind As Range

Application.ScreenUpdating = False
  
With Sheet1
    Set rFind = .Cells.Find(what:="Summary", lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        .Range(.Cells(rFind.Row + 1, "K").End(xlToRight), .Cells(Rows.Count, "K").End(xlUp)).Select
    End If
End With

Application.ScreenUpdating = True

End Sub

Open in new window

Btw does assume in your actual data you won't have two tables.

Author

Commented:
Stephen, this is very close to what I need.  It worked perfectly for the instance of have only one row below the heading row.  But for mulitple rows existing below the heading, it selected too many rows.  Please see my screen shot.

 Screen print
Also your assumption is correct.  You will never have two tables with the same table name (e.g. you would never have two tables title "Summary")

But you will have one table under another?
Try this:
If Not rFind Is Nothing Then
        With .Cells(rFind.Row + 1, "K").CurrentRegion
            .Offset(1).Resize(.Rows.Count - 1).Select
        End With
    End If

Open in new window

Author

Commented:
Yes, there could be multiple tables one under the other.  I'll test this bit of code and get back to you...

Author

Commented:
Perfect!  This is what I needed.  Thanks so much!!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.