分類:[Microsoft Office 全般]
2021/11/03(Wed) 10:03:44 編集(投稿者)
お世話になります。
エクセルでのVBAの勉強を始めました。初心者です。
以下のようなエクセルのセルがあります。
VBAがわからないので、あるHPからコードをとってきたのですが
2B-8D を選択して【表示】-【マクロ】-【アウトライン_列下げ階層】を
実行すると400 というエラーが発生します。
なにが間違っているのでしょうか?
使用しているエクセルは2013です。
--------------------------------------------------
A B C D E
1
2 A
3 B
4 C
5 D
6 E
7 F
8 G
9
10
--------------------------------------------------
Option Explicit
Sub アウトライン_列下げ階層()
outlineTree probe:="columnPosition"
End Sub
Private Sub outlineTree(probe As String)
If TypeName(Selection) <> "Range" Then Beep: Exit Sub
Dim titleRng As Range
Set titleRng = Intersect(ActiveSheet.UsedRange, Selection.Areas(1))
If titleRng Is Nothing Then Beep: Exit Sub
Application.ScreenUpdating = False
ActiveSheet.Outline.SummaryRow = xlAbove
titleRng.ClearOutline
Call traverseList(titleRng, 0, probe:=probe, doProc:="doGroup")
Application.ScreenUpdating = True
End Sub
Private Function traverseList(curRng As Range, curLevel As Integer, probe As String, doProc As String) As Range
Dim i As Integer
For i = 1 To curRng.Rows.Count - 1
Dim subRng As Range
Dim nextLevel As Integer
Set subRng = Intersect(curRng, curRng.Offset(i))
nextLevel = Application.Run(probe, subRng.Rows(1), curLevel)
If nextLevel > curLevel Then
Set subRng = traverseList(subRng, nextLevel, probe, doProc)
Set subRng = Application.Run(doProc, subRng, nextLevel)
i = i - 1 + subRng.Rows.Count
ElseIf nextLevel < curLevel Then
Exit For
End If
Next
Set traverseList = curRng.Resize(i)
End Function
Private Function columnPosition(itemRow As Range, level As Integer) As Integer
columnPosition = 0
Dim c As Range
For Each c In itemRow.Cells
If Not IsEmpty(c) Then Exit Function
columnPosition = columnPosition + 1
Next
End Function
Private Function doGroup(ByVal rng As Range, level As Integer) As Range
rng.Rows.Group
Set doGroup = rng
End Function