參照資料列和資料欄

使用 Rows 屬性或 Columns 屬性來處理整個數據列或數據行。 這些屬性可傳回代表儲存格範圍的範圍物件。 在下列範例中，Rows(1) 會傳回 Sheet1 上的第一列。 然後將該範圍字型物件的粗體屬性設定為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 服務的 .NET Framework 解決方案。 Dennis 在開發 Excel 解決方案方面已經有超過 20 年的經驗，也是 "Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA and .NET (2nd Edition)" 的共同作者。

支援和意見反應

有關於 Office VBA 或這份文件的問題或意見反應嗎？ 如需取得支援服務並提供意見反應的相關指導，請參閱 Office VBA 支援與意見反應。