VBA程式教學:計算Excel報表資料筆數及標記空白列

工作中常遇到報表有空白列,本文以應付帳款明細報表為例,介紹UsedRange確認報表資料範圍,操作End(xlDown).Row和End(xlUp).Row快速定位,用Range和Cells表達儲存格,再以IsEmpty標記空白列。

一、無資料空白列

系統產生的應付帳款明細報表裡有空白列,可能會對閱讀或分析上造成不便,需要加以處理。

VBA程式教學:計算Excel報表資料筆數及標記空白列 1

二、UsedRange使用範圍

於Excel按下組合鍵「Alt + F11」進入VBA,輸入程式碼:

Sub A報表使用範圍()

資料筆數A = ActiveSheet.UsedRange.Rows.Count

MsgBox 資料筆數A

End Sub

第一步先利用VBA程式碼「UsedRange.Rows.Count」計算報表資料使用範圍,前面加上「ActiveSheet.」,表示要統計目前操作的工作表裡總共有多少列訊息,再由訊息視窗MsgBox提供資料筆數的數值。

注意到上述的程式碼係以工作表為對象,因此無法精準以某一特定欄作為條件。

VBA程式教學:計算Excel報表資料筆數及標記空白列 3

三、計算報表列數

參考上個步驟截圖,VBA編輯器上方工具列的「執行」下拉,點選「執行Sub或UserForm F5」這個指令便可以執行程式(快速鍵F5),應該會立即看到Excel跳出顯示「16」的訊息視窗,表示此工作表共有16列數據。

除了在VBA編輯器執行程式,亦能於Excel工作表將此巨集設為按紐,如同截圖命名為「執行程式」,點選即能執行VBA程式碼,詳細步驟可參考先前文章「Excel將格式設定錄製巨集,快速鍵及命令按鈕執行」。

VBA程式教學:計算Excel報表資料筆數及標記空白列 5

四、儲存格快速定位

也能利用儲存格快速定位,計算報表資料筆數,在Excel按下鍵盤快速鍵「Ctrl+方向鍵」,就可以依照方向直接前往最後一個有資料內容的儲存格。若以VBA程式自動化執行此功能,要輸入以下程式碼:

Sub B儲存格快速定位()

資料筆數A = Range(“A1”).End(xlDown).Row

MsgBox 資料筆數A

資料筆數B = Range(“G1048576”).End(xlUp).Row

MsgBox 資料筆數B

End Sub

「資料筆數A」的程式碼是從儲存格A1一直往下,也就是剛才用指令操作的Control加下,它會從A1開始到有資料的最後一列。「1048576」是Excel的最後一列資料,「資料筆數B」的程式碼指的是從儲存格G1048576往上到G欄的最後一筆資料。

和先前第二步驟兩相比較,很容易可以發現這裡的程式碼是以A欄或G欄作為判斷報表資料範圍的條件,因此較為靈活而且精準。

VBA程式教學:計算Excel報表資料筆數及標記空白列 7

五、End(xlDown).Row

執行結果會跳出訊息視窗顯示從儲存格A1開始往下算,共有3筆資料。

VBA程式教學:計算Excel報表資料筆數及標記空白列 9

六、End(xlUp).Row

在前述訊息視窗點選「確定」後,會再跳出顯示「16」的訊息欄位,也就是從G1048576往上到G欄最後一筆資料,所在位置即G16。

VBA程式教學:計算Excel報表資料筆數及標記空白列 11

七、VBA IsEmpty函數

確認完資料筆數後,就可以設計迴圈流程。將Step等差設定為-1,從最後一筆資料遞減到2,由下往上執行迴圈。VBA的IsEmpty函數,可以判斷內容是否空值,若儲存格Range(“A” & i)為空值,就在儲存格第i列第H欄寫入1,以Cells(i, “H”)表達;在Excel裡,儲存格是最基本也最常使用的操作對象,因此這個使用的Range和Cells應該要很熟悉運用。

完整的VBA程式碼如下:

資料筆數 = Range(“G1048576”).End(xlUp).Row

For i = 資料筆數 To 2 Step -1

If IsEmpty(Range(“A” & i)) Then Cells(i, “H”) = 1

Next i

End Sub

VBA程式教學:計算Excel報表資料筆數及標記空白列 13

八、標記報表空白列

結果如圖所示,儲存格A16到A2間若有空值,就會在該列的H欄填入「1」,等於是標記了空白列,後續以此為基礎便能再進一步整理資料。

VBA程式教學:計算Excel報表資料筆數及標記空白列 15

善用VBA從Excel挖堀資料

現今的工作很仰賴資訊系統提供資料,但是產出的報表往往會有許多空白欄位,造成閱讀和作業的不便,人工刪除或加工不僅耗時也容易出錯,這時候就交由VBA自動化處理,把重複性的工作變成輕鬆省事,在大數據術語中,通常會這個流程稱之為資料清洗,在執行分析第一步最重點的便是去蕪存菁,才有可能從原始資料中挖出金礦。

每天學習,每天充電:VBA自動化文章合輯