VBA组合 [英] Group By With VBA

问题描述

我有一个工作表有一个标题行，我想使用VBA对行进行分组。我尝试过这个语法

  Sub GroupItTogether（）
 Dim rLastCell As Range 
设置rLastCell = ActiveSheet。 Cells.Find（What：=*，After：=。Cells（1，1），_ 
 LookIn：= xlFormulas，LookAt：= _ 
 xlPart，SearchOrder：= xlByRows，SearchDirection = xl上一个，MatchCase：= False）
范围（A2& rLastCell）。选择
 Selection.Rows.Group 
 ActiveSheet.Outline.ShowLevels RowLevels：= 1 
 End Sub 
  

但是，这会产生以下错误：

无效或不合格参考

突出显示代码行：之后：=。单元格（1，1）

我需要做什么来将所有行（标题）与VBA分组？ >

编辑

每个评论，我将我的语法编辑到下面，删除错误但是这并不对所有行进行分组（不包括头文件）。应该如何更新为按使用范围进行分组？

  Sub GroupItTogether（）
 Dim rLastCell As Range 
设置rLastCell = ActiveSheet.Cells.Find（What：=*，After：= Cells（1，1），_ 
 LookIn：= xlFormulas，LookAt：= _ 
 xlPart，SearchOrder： = xlByRows，SearchDirection：= xlPrevious，MatchCase：= False）
范围（A2& rLastCell）。选择
 Selection.Rows.Group 
 ActiveSheet.Outline.ShowLevels RowLevels：= 1 
 End Sub 
  

解决方案

你不需要使用选择和选择。找到 rLastCell 的范围后，您可以从 rLastCell.Row 读取范围的最后一行属性，然后将它们分组。

  Option Explicit 
 
 Sub GroupItTogether（）
 
 Dim rLastCell As Range 
 
设置rLastCell = ActiveSheet.Cells.Find（What：=*，After：= Cells（1，1），_ 
 LookIn：= xlFormulas，LookAt ：= _ 
 xlPart，SearchOrder = = xlByRows，SearchDirection：= xlPrevious，MatchCase：= False）
 
范围（A2：A& rLastCell.Row）.Rows.Group 
 ActiveSheet.Outline.ShowLevels RowLevels：= 1 
 
 End Sub 
  

注意：您可以通过以下方式获取列A中具有数据的最后一行：

  lastrow =单元格（Rows.Count，A）。End（xlUp）.Row 
  

（不需要使用 .Find 方法）

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

解决方案

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)

这篇关于VBA组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！