VBA｜使用Range对象03：实现行、列操作

在工作表操作中，对行列的操作是最常用的操作之一，如插入、删除、隐藏、设置行高（列宽）等。通过使用Range对象的属性和方法，也可以实现同样的操作。

目录

1 用Rows的Insert方法插入行

2 用Column的Insert方法插入列

3 用Delete方法删除列

4 用Hidden属性隐藏行

5 用HowHeight属性设置行高

6 用ColumnWidth属性设置列宽

1 用Rows的Insert方法插入行

Sub 插入行()

Dim r As Long

r = Selection.Row

ActiveSheet.Rows(r).Insert

End Sub

2 用Column的Insert方法插入列

Sub 插入列()

Dim c As Long

c = Selection.Column

ActiveSheet.Columns(c).Insert

End Sub

3 用Delete方法删除列

Sub 删除空行()

Dim rng As Range

Set rng = Columns(1).SpecialCells(xlCellTypeBlanks)

rng.EntireRow.Delete

End Sub

4 用Hidden属性隐藏行

Sub 隐藏行()

r = ActiveCell.Row

ActiveSheet.Rows(r).Hidden = True

End Sub

5 用HowHeight属性设置行高

Sub 设置行高()

Dim h As Long, r As Long, i As Integer, n As Integer

Dim ws1 As Worksheet

h = Application.InputBox(prompt:='请输入所选行的高度：', _

Title:='输入行高', Type:=1)

Set ws1 = ActiveSheet

n = Selection.Rows.Count '选中区域的行数

r = ActiveCell.Row

For i = 1 To n

ws1.Rows(r + i - 1).RowHeight = h

Next

Set ws1 = Nothing

End Sub

6 用ColumnWidth属性设置列宽

Sub 设置列宽()

Dim w As Long, c As Long, i As Integer, n As Integer

Dim ws1 As Worksheet

w = Application.InputBox(prompt:='请输入所选列的宽度：', _

Title:='输入列度', Type:=1)

If w = 0 Then Exit Sub

Set ws1 = ActiveSheet

n = Selection.Columns.Count

c = ActiveCell.Column

For i = 1 To n

ws1.Columns(c + i - 1).ColumnWidth = w

Next

Set ws1 = Nothing

End Sub

－End－