VBA判斷空白儲存格:Excel隱藏列號及刪除空白列

VBA可以高效率處理Excel報表,本文以應付帳款為例,介紹正逆向迴圈語句加流水號,IsEmpty函數判斷隱藏空白列,MsgBox以vbNewLine換行顯示相關資訊,最後再刪除空白列。

一、應付帳款明細表

以下方的應付帳款明細表為例,實務上可能想要加上可供索引的流水號資訊,或者刪除空白列。

VBA判斷空白儲存格:Excel隱藏列號及刪除空白列 1

二、正迴圈與逆迴圈

流水號的設計協助使用者處理資料編號或排序作業,利用VBA迴圈語法可以在需要的欄位快速寫入流水號碼。正迴圈從第一筆資料由上到下於H欄增加流水號,流水號碼與列數一致,逆迴圈則從最後一筆資料執行程式,依Step等差-1遞減,並於I欄位填入流水號碼。

完整的VBA程式碼如下:

Sub A_正迴圈與逆迴圈()

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

For i = 2 To 資料筆數 Step 1

Cells(i, “H”) = i

Next i

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

Cells(j, “I”) = j

Next j

End Sub

VBA判斷空白儲存格:Excel隱藏列號及刪除空白列 3

三、報表增加流水號

在VBA編輯頁面上方工具列依序點選【執行>執行Sub或UserForm】,或者以【F5】快速鍵執行,就可以看到報表出現流水號。

關於Excel如何執行VBA程式,可參考贊贊小屋相關文章

VBA判斷空白儲存格:Excel隱藏列號及刪除空白列 5

四、IsEmpty判斷空白列

先前贊贊小屋相關文章有提過IsEmpty是VBA函數,可以判斷內容是否為空值,判定後再決定下一步驟,因此可以進一步利用Rows.Hidden語法隱藏空白列。完整的程式碼如下所示:

Sub B_空白列隱藏()

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

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

If IsEmpty(Range(“A” & i)) Then Rows(i).Hidden = True

Next i

End Sub

VBA判斷空白儲存格:Excel隱藏列號及刪除空白列 7

五、空白列隱藏

執行程式後會發現報表空白列不見了,也可以從Excel左側的列數不連貫,發現有隱藏的空白列。

VBA判斷空白儲存格:Excel隱藏列號及刪除空白列 9

六、vbNewLine換行

下列VBA程式會判讀Excel裡的隱藏列,並跳出訊息視窗加以說明,為了排版美觀及資訊閱讀性,另在MsgBox函數加上vbNewLine程式碼進行換行操作。

Sub C_哪些列被隱藏了()

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

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

If Range(“A” & i).EntireRow.Hidden Then

隱藏列 = “第” & i & “列” & vbNewLine & 隱藏列

End If

Next i

MsgBox “隱藏列有:” & vbNewLine & 隱藏列

End Sub

VBA判斷空白儲存格:Excel隱藏列號及刪除空白列 11

七、MsgBox隱藏列

執行上述程式碼後,報表跳出訊息視窗並說明隱藏列列數,每條資訊都換行顯示,也可以和左側列數欄位相對照。

注意到由於Excel的Ctrl+上或下會忽略掉隱藏的資料,因此「資料筆數 = Range(“G1048576”).End(xlUp).Row」的執行結果其實是14,從14列開始起算,所以這裡的訊息文字並沒有15和16,雖然這兩列同樣被隱藏了。

VBA判斷空白儲存格:Excel隱藏列號及刪除空白列 13

八、Rows.delete刪除列

沿用前面類似結構,利用VBA程式碼找出空白列,並使用Rows.delete函數刪除空白列。特別要注意的是,為了配合Excel刪除列後自動往上遞補特性,必須使用逆迴圈。完整的程式碼如下:

Sub D_空白列刪除()

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

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

If Range(“A” & i) = “” Then

Rows(i).Delete

End If

Next i

End Sub

VBA判斷空白儲存格:Excel隱藏列號及刪除空白列 15

九、報表刪除空列

執行VBA程式後,報表空白列果然都消失了,也因為進行刪除動作,所以左側列數依序增加,不會再有不連續的狀況了。

VBA判斷空白儲存格:Excel隱藏列號及刪除空白列 17

逐步積累Excel VBA功力

隨著認識的VBA函數越來越多,對資料的處理也更加多元,以本文的IsEmpty為例,找出空白列以後,能運用不同的程式碼加以標記、隱藏和刪除,大大增加工作的便利性和靈活度。日常工作中,細瑣重複性地工作無可避免,但往往是更有價值產出的初期工序,在Excel只能直覺性地手動作業外,VBA程式ibpp 提供極有效率的解決方案,歡迎大家跟著贊贊小屋一步一步堆疊你的VBA超能力!

完整學習:VBA自動化文章合輯