请参阅行和列
使用 Rows 属性或 Columns 属性来处理整行或整列。 这些属性返回一个 Range 对象,该对象代表单元格区域。 在以下示例中, Rows(1)
返回 Sheet1 上的第一行。 然后,该区域的 Font 对象的 Bold 属性设置为 True。
Sub RowBold()
Worksheets("Sheet1").Rows(1).Font.Bold = True
End Sub
下表举例说明了使用 Rows 和 Columns 属性的一些行和列的引用。
Reference | 含义 |
---|---|
Rows(1) |
第一行 |
Rows |
工作表上所有的行 |
Columns(1) |
第一列 |
Columns("A") |
第一列 |
Columns |
工作表上所有的列 |
若要同时处理若干行或列,请创建一个对象变量并使用 Union 方法,将对 Rows 属性或 Columns 属性的多个调用组合起来。 下例将活动工作簿中第一张工作表上的第一行、第三行和第五行的字体设置为加粗。
Sub SeveralRows()
Worksheets("Sheet1").Activate
Dim myUnion As Range
Set myUnion = Union(Rows(1), Rows(3), Rows(5))
myUnion.Font.Bold = True
End Sub
提供的示例代码: Dennis Wallentin、 VSTO & .NET & Excel 本示例删除所选区域中的空行。
Sub Delete_Empty_Rows()
'The range from which to delete the rows.
Dim rnSelection As Range
'Row and count variables used in the deletion process.
Dim lnLastRow As Long
Dim lnRowCount As Long
Dim lnDeletedRows As Long
'Initialize the number of deleted rows.
lnDeletedRows = 0
'Confirm that a range is selected, and that the range is contiguous.
If TypeName(Selection) = "Range" Then
If Selection.Areas.Count = 1 Then
'Initialize the range to what the user has selected, and initialize the count for the upcoming FOR loop.
Set rnSelection = Application.Selection
lnLastRow = rnSelection.Rows.Count
'Start at the bottom row and work up: if the row is empty then
'delete the row and increment the deleted row count.
For lnRowCount = lnLastRow To 1 Step -1
If Application.CountA(rnSelection.Rows(lnRowCount)) = 0 Then
rnSelection.Rows(lnRowCount).Delete
lnDeletedRows = lnDeletedRows + 1
End If
Next lnRowCount
rnSelection.Resize(lnLastRow - lnDeletedRows).Select
Else
MsgBox "Please select only one area.", vbInformation
End If
Else
MsgBox "Please select a range.", vbInformation
End If
'Turn screen updating back on.
Application.ScreenUpdating = True
End Sub
本示例将删除所选范围中的空白列。
Sub Delete_Empty_Columns()
'The range from which to delete the columns.
Dim rnSelection As Range
'Column and count variables used in the deletion process.
Dim lnLastColumn As Long
Dim lnColumnCount As Long
Dim lnDeletedColumns As Long
lnDeletedColumns = 0
'Confirm that a range is selected, and that the range is contiguous.
If TypeName(Selection) = "Range" Then
If Selection.Areas.Count = 1 Then
'Initialize the range to what the user has selected, and initialize the count for the upcoming FOR loop.
Set rnSelection = Application.Selection
lnLastColumn = rnSelection.Columns.Count
'Start at the far-right column and work left: if the column is empty then
'delete the column and increment the deleted column count.
For lnColumnCount = lnLastColumn To 1 Step -1
If Application.CountA(rnSelection.Columns(lnColumnCount)) = 0 Then
rnSelection.Columns(lnColumnCount).Delete
lnDeletedColumns = lnDeletedColumns + 1
End If
Next lnColumnCount
rnSelection.Resize(lnLastColumn - lnDeletedColumns).Select
Else
MsgBox "Please select only one area.", vbInformation
End If
Else
MsgBox "Please select a range.", vbInformation
End If
'Turn screen updating back on.
Application.ScreenUpdating = True
End Sub
关于参与者
Dennis Wallentin 是 VSTO & .NET & Excel 的作者,该博客专注于适用于 Excel 和 Excel Services 的 .NET Framework 解决方案。 Dennis 已经从事 Excel 解决方案开发超过 20 年,同时也是“专业 Excel 开发:使用 Microsoft Excel、VBA 和 .NET 开发应用程序的权威指南(第 2 版)”的合著者。
支持和反馈
有关于 Office VBA 或本文档的疑问或反馈? 请参阅 Office VBA 支持和反馈,获取有关如何接收支持和提供反馈的指南。
反馈
https://aka.ms/ContentUserFeedback。
即将发布:在整个 2024 年,我们将逐步淘汰作为内容反馈机制的“GitHub 问题”,并将其取代为新的反馈系统。 有关详细信息,请参阅:提交和查看相关反馈