列
幅
幅
Sub 列_幅()
'列幅を自動調整する。 With Selection(1) If .ColumnWidth > 100 Then Selection.ColumnWidth = 100 ElseIf .ColumnWidth > 50 Then Selection.ColumnWidth = 50 ElseIf .ColumnWidth > 25 Then Selection.ColumnWidth = 25 ElseIf .ColumnWidth > 10 Then Selection.ColumnWidth = 8.1 ElseIf .ColumnWidth > 2 Then Selection.ColumnWidth = 2 ElseIf .ColumnWidth > 1 Then Selection.ColumnWidth = 1 Else Selection.ColumnWidth = 200 End If
End With End Sub
'列幅を自動調整する。 With Selection(1) If .ColumnWidth > 100 Then Selection.ColumnWidth = 100 ElseIf .ColumnWidth > 50 Then Selection.ColumnWidth = 50 ElseIf .ColumnWidth > 25 Then Selection.ColumnWidth = 25 ElseIf .ColumnWidth > 10 Then Selection.ColumnWidth = 8.1 ElseIf .ColumnWidth > 2 Then Selection.ColumnWidth = 2 ElseIf .ColumnWidth > 1 Then Selection.ColumnWidth = 1 Else Selection.ColumnWidth = 200 End If
End With End Sub
灰色列非表示
灰色列非表示
Sub 列_灰色列非表示()
'選択範囲で灰色のセルの列を非表示にする。 Dim s As Worksheet Dim r_start As Long Dim r_end As Long Dim c_start As Long Dim c_end As Long Dim i As Long Dim j As Long
If Selection.Rows.Count > 1 Then MsgBox "1行だけ選択してください。" Exit Sub End If
Selection.Columns.Hidden = False
Set s = ActiveSheet With s.UsedRange r_start = .Row r_end = .Row + .Rows.Count - 1 c_start = .Column c_end = .Column + .Columns.Count - 1 End With With Selection If .Row > r_start Then r_start = .Row End If If .Row + .Rows.Count - 1 < r_end Then r_end = .Row + .Rows.Count - 1 End If If .Column > c_start Then c_start = .Column End If If .Column + .Columns.Count - 1 < c_end Then c_end = .Column + .Columns.Count - 1 End If End With For j = c_start To c_end If s.Cells(r_start, j).Interior.ColorIndex = 15 Or s.Cells(r_start, j).Interior.ColorIndex = 16 Then s.Columns(j).Hidden = True End If Next j
End Sub
'選択範囲で灰色のセルの列を非表示にする。 Dim s As Worksheet Dim r_start As Long Dim r_end As Long Dim c_start As Long Dim c_end As Long Dim i As Long Dim j As Long
If Selection.Rows.Count > 1 Then MsgBox "1行だけ選択してください。" Exit Sub End If
Selection.Columns.Hidden = False
Set s = ActiveSheet With s.UsedRange r_start = .Row r_end = .Row + .Rows.Count - 1 c_start = .Column c_end = .Column + .Columns.Count - 1 End With With Selection If .Row > r_start Then r_start = .Row End If If .Row + .Rows.Count - 1 < r_end Then r_end = .Row + .Rows.Count - 1 End If If .Column > c_start Then c_start = .Column End If If .Column + .Columns.Count - 1 < c_end Then c_end = .Column + .Columns.Count - 1 End If End With For j = c_start To c_end If s.Cells(r_start, j).Interior.ColorIndex = 15 Or s.Cells(r_start, j).Interior.ColorIndex = 16 Then s.Columns(j).Hidden = True End If Next j
End Sub