Group By With VBA

问题:

I have a worksheet that has a header row, and I want to group the rows using VBA. I have attempted this syntax

Sub GroupItTogether()
  Dim rLastCell As Range
  Set rLastCell = ActiveSheet.Cells.Find(What:="*", After:=.Cells(1, 1),  _   
    LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
  Range("A2" & rLastCell).Select
  Selection.Rows.Group
  ActiveSheet.Outline.ShowLevels RowLevels:=1
End Sub

However, this will produce an error of:

Invalid or unqualified reference

Highlighting the line of code: After:=.Cells(1, 1)

What must I do to group all rows (sans the header) with VBA?

EDIT

Per comments, I edited my syntax to the below, which removes the error but this does not group all rows (excluding header). How should this be updated to group by the used range?

  Sub GroupItTogether()
  Dim rLastCell As Range
  Set rLastCell = ActiveSheet.Cells.Find(What:="*", After:=Cells(1, 1), _    
    LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
  Range("A2" & rLastCell).Select
  Selection.Rows.Group
  ActiveSheet.Outline.ShowLevels RowLevels:=1
End Sub

回答1:

You don't need to use Select and Selection. Once you find the Range for rLastCell , you can read the last row property from your range with rLastCell.Row, and then just group them.

Option Explicit

Sub GroupItTogether()

Dim rLastCell As Range

Set rLastCell = ActiveSheet.Cells.Find(What:="*", After:=Cells(1, 1), _
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)

Range("A2:A" & rLastCell.Row).Rows.Group
ActiveSheet.Outline.ShowLevels RowLevels:=1

End Sub

Note: you can get the last row that has data in Column A with :

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

(no need to use the .Find method)