VBA如何設置迴圈將多餘資料刪除

綜合本書到目前為止的內容,可知利用Excel取得網頁資料並不難,麻煩的是整批量下載的內容,於工作表上會有很多不必要的東西,例如重覆的標題行和與核心資料無關的訊息,所幸於整理資料表這方面,Excel剛好是專家,再搭配VBA可以達到很高效率的作業。這一節便以歷史匯率為範例,介紹如何適當地刪除多餘的資料。

一、結合第三節利用「QueryTables.Add」取得網頁匯率的程式碼、和上一節利用「Application.InputBox」輸入年份幣別的方法,編寫VBA程式碼如圖所示。

QueryTables.Ad

二、執行程式,選擇2016年的港幣(HKD)。為了方便截圖,直接取得的資料在格式上已經先調整了欄寛,並且把日期隱藏起來,但是有兩個問題,一個是圖片綠色部份,標題欄位的格式跑掉了,另一個是圖片黃色部份,在不同月份之間會有空行存在。

選擇2016年的港幣(HKD)

三、編寫刪除空白行的程式碼,意思是找出A欄中,儲存格特殊狀態為空白的對象物件,然後執行刪除整行的方法。這一行程式碼為慣常用法,看起來有點複雜,但一經拆解,其實在架構上仍然是「物件.方法」的標準VBA語法。

編寫刪除空白行的程式碼

四、執行「DeleteRows」巨集程式,結果如圖所示,原本黃色部份的空行、包括一部份綠色A欄為空格,整行都被刪除了,不過仍然有多餘的標題欄存在。

執行「DeleteRows」巨集程式

五、不同版本的Excel最大行數也不同,以作者2013年的Office而言,在任一工作表,按住「Ctrl」鍵再按方向鍵「下」,直接跳到最後一行,如圖所示為「1048576」,這便是目前Excel最大行數。如果想要針對整張工作表作整理,例如刪除含某特定內容儲存格所在的行,首先必須先知道最大行數。

不同版本的Excel最大行數也不同

六、編寫程式如圖所示,設置以i為變數的迴圈,從「1048576」到「2」,「Step -1」表示為遞減順序,保留第一行,是因為第一行應當為標題欄。在迴圈裡設計儲存格內容的判斷式,滿足條件時刪除第i行(Rows(i).Delete)。

設置以i為變數的迴圈

七、執行結果如同預期,成功將其他多餘的資料都刪除了,只保留匯率的部份,截圖是每個月顯示三天,其餘隱藏,另外也手工修改了標題欄。

成功將其他多餘的資料都刪除了

這一節介紹兩種方法刪除資料,其一是刪除空行,其二是刪除特定內容的整行。依照範例程式碼,必須兩個方法的巨集都執行過,才能預期的效果。其實如果理解了刪除特定內容的程式碼精髓,在程式設上可以很靈活,設計出一次到位的程序,擴大而言,視不同網頁的結構狀況,以類似程式設計,都可以在下載資料的同時,把多餘不必要的內容刪除掉。在本書後面的章節,會再針對其他網頁分享如何整理所取得的資料。

本文內容取自《人人做得到的網路資料整理術》,博客來書店網址:

http://www.books.com.tw/products/0010775391