余小章 @ 大內殿堂

1.我們可以使用UsedRange屬性來取得工作表已使用的範圍

    Dim myRange As Range
    Set myRange = Sheet1.UsedRange
    myRange.Select
    MsgBox "最下一列:" & myRange.Rows.Count & vbCrLf & _
           "最右一行:" & myRange.Columns.Count
End Sub

2.這個方法會將已使用的儲存格計算進去；不管儲存格是否為空，只要有定義儲存格的設定，則會被計算。與上篇的[VBA] 如何取得工作表中的最後一欄 / 列 非空白儲存格不同，但各有各的好處。

3.使用此法可以使用一些條件過濾掉空白的欄位；或是直接刪除空白列。可以用Application.WorksheetFunction.Count方法或Application.WorksheetFunction.CountA方法來判斷是否為空列 / 欄

Sub DeleteBlankRow()
    Dim FirstRow As Long, LastRow As Long, i As Long
    Dim myRange As Range
    Set myRange = Sheet1.UsedRange
    '第一列
    FirstRow = myRange.Row
    '最後一列
    LastRow = FirstRow + myRange.Rows.Count - 1
    For i = LastRow To FirstRow Step -1
        '判斷是否為空列
        If Application.WorksheetFunction.Count(Rows(i)) = 0 Then
            Rows(i).Delete '刪除
        End If
    Next
End Sub
'刪除空白欄
Sub DeleteBlankColumns()
    Dim FirstCol As Long, LastCol As Long, i As Long
    Dim myRange As Range
    Set myRange = Sheet1.UsedRange
    '第一欄
    FirstCol = myRange.Column
    '最後一欄
    LastCol = FirstCol + myRange.Columns.Count - 1
    For i = LastCol To FirstCol Step -1
        '判斷是否為空列
        If Application.WorksheetFunction.Count(Columns(i)) = 0 Then
            Columns(i).Delete '刪除
        End If
    Next
End Sub

                    
若有謬誤，煩請告知，新手發帖請多包涵

Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET