VBA利用If then找出關鍵字,For Next迴圈刪除多餘資料

Excel原始報表可能有許多不必要資料︳本文介紹VBA如何用If Then條件判斷找出標記關鍵字,以Usedrange確定資料範圍,For Next迴圈檢查整份報表,最後Rows.Delete刪除多餘資料。

VBA程式長處在於批次大量的依照規則重複執行Excel操作,其中最關鍵的是上一節介紹的迴圈事件。上一節範圍是很簡單的1到100,可是實務上每次處理的資料筆數不同,如果每份資料都要在程式裡設定一個特定的迴圈次數,顯然不是很聰明。

這一節介紹如何設計VBA程式先確定資料的筆數,再自動決定迴圈範圍。另外當然也希望執行每一趟迴圈時程式能做一些思考惟判斷,因此也會跟各位介紹If邏輯判斷事件,它和迴圈事件兩者可說是VBA程式自動化的兩大支柱。

一、網頁結構變動

上一節程式執行後發現有點問題。「TOP1」並沒有出現「保留行」,資料錯位了,應該是從93列開始,現在是從94列開始。這是因為網頁結構本身有所改變,「TOP1」出現位置從94列變成93列,程式是固定常數94,因此會出錯。

VBA利用If then找出關鍵字,For Next迴圈刪除多餘資料 1

二、If then條件判斷

先前介紹過設定變數的好處是不特定代名詞,為了避免上個步驟的錯誤,在此把「TOP1」設定為不確定的變數,並且運用IF邏輯判斷事件:

KeyW = ActiveSheet.Cells(i, 1).Value:將目前工作表Cell(i,1)的值設定為變數KeyW值。

If KeyW = “TOP1” Then:「If+條件式Then」隔行後是符合條件時要執行的程式,最後以「End If」結束。VBA是以一行一行程式碼作為單位,注意要遵守固定的隔行規則。

綜合起來,「TOP1標記」程序是目前工作表從1到100行逐行迴圈執行,如果有儲存格內容是「TOP1」的話,依照先前規則在同一行相對第五欄寫入「保留行」。

VBA利用If then找出關鍵字,For Next迴圈刪除多餘資料 3

三、程式標記關鍵字

執行程式後,果然在1到100行的範圍內,有出現「TOP1」的關鍵行,該行連同接著的第1、4、5行都寫入了「保留行」,程式在這裡成功執行了邏輯判斷。

VBA利用If then找出關鍵字,For Next迴圈刪除多餘資料 5

四、迴圈流程控制

先前程式主要是介紹IF邏輯判斷事件的用法,設定的迴圈只有100列,在此要把範圍擴大,首先要先確定資料的範圍有多少,這個可以利用VBA的UsedRange指令,另外先前是以「TOP1」作為判斷條件,配合現在範圍擴大,而且TOP1到TOP100都有相同的「TOP」,這可以使用VBA的Left函數進行文字轉換。

隨著本書程式碼越來越多,為方便說明起見,把程式碼都標上列號:

80|UsedR = Worksheets(Today2).UsedRange.Rows.Count:確定工作表上使用範圍,計算這個範圍有多少列,將列數作為變數值(UsedR)。

100|MsgBox “這個工作表的使用範圍有多少欄:” & UsedR:利用MsgBox提示第80行程式計算的列數。

130|For i = 1 To UsedR:設定1到UsedR(資料列數)的迴圈事件,等於會涵蓋整個資料表。

160|KeyW = Left(KeyW, 3):VBA的Left函數取得左邊起三個文字,作用和Excel的LEFT函數相同。所以如果TOP1、TOP2、…TOP100的話,經過函數轉換後都是TOP。
180|If KeyW = “TOP” Then:配合設定好的TOP關鍵字作為判斷條件。

VBA利用If then找出關鍵字,For Next迴圈刪除多餘資料 7

五、全資料範圍標記

執行結果果然跳出視窗提示目前資料範圍總共有758列,成功把100個排行榜項目都依照規則標記「保留行」。

VBA利用If then找出關鍵字,For Next迴圈刪除多餘資料 9

六、自動刪除資料

230|For i = UsedR To 1 Step -1:先前迴圈事件都是從1開始的順序,其實也可以做其他變化,這裡將Step等差設定為-1,會從UsedR(使用範圍758列)遞減到1執行迴圈程式。

260|If KeyW <> “保留行” Then:VBA裡設定不等於的方式和Excel相同,都是<>。

270|Rows(i).Delete:刪除第i列。VBA裡沒有特地指定的話,表示是目前活頁簿目前工作表,也可以把它理解成是省略了ThisWorkbook和ActiveSheet。
310|Columns(5).Delete:刪除第5欄。

VBA利用If then找出關鍵字,For Next迴圈刪除多餘資料 11

七、自動整理報表

經過實際測試,這個程式成功完成本小節目標,將所取得的網頁多餘資料删除。

VBA利用If then找出關鍵字,For Next迴圈刪除多餘資料 13

依操作流程設計程式

本節因為是承接上一節程式作為擴充,同時也為了方便依序講解VBA重要指令的需要,程式的編寫比較符合循序漸進的思惟邏輯,但有可能不是那麼精簡有效率。以範例程式來說,先在工作表上標記資料,然後依照標記資料進行删除,最後再把標記資料删掉,其實應該也可以在程式設計中不用標記直接刪掉多餘資料。

像上述情況,設計VBA本來就跟Excel函數公式一樣,可能有很多種方法都可以達到相同任務,不過基本原則是一樣的,在VBA裡就是迴圈事件和邏輯判斷,讀者有興趣可以試看看用不同的程式碼實現和本節相同的效果。

本文內容取自《人人都學得會的網路大數據分析入門》,書本仍然是最好的學習方法,省下一張電影票的錢,今天就買本書吧!