因為感謝的事情太多了

所以就謝天吧

攝影師:Negative Space,連結:Pexels

2020年09月20日,我為了專案的測試計畫能更自動的撰寫,研究了EXCEL巨集(VBA語法),雖然網路上資源很多,有很多很厲害的語法,但是對於初學者來說,如何思考要使用什麼樣的資源,並寫下自己改良的code,這思考的流程,我想要把它紀錄下來,這一系列的文章,總共會分為以下主題,如你有興趣可以直接連接到那一篇去看。

第一個部分,合併儲存格也能自動設定行高(列高),為什麼需要這個語法?

在不改變欄寬時,我們通常會使用自動換列加excel自適應行高(列高)來讓所有文字可以顯示出來,此時只要在那一列的最前面點兩下,就會展開。

這樣的語法如要使用VBA表達則是

Selection.EntireRow.AutoFit  
'Autofit的語法是自適應行高(列高)的意思

但是如果是合併儲存格,就算你有自動換列,EXCEL也會當成只有一行,所以上述語法使用之後,仍然無法展開。

所以,我在Google搜尋了「vba自動讓列高變成內容大小」,找到了一個相關資源。

VBA|動態設置行高(根據某一列每行的字符數量)

原文網址:https://kknews.cc/tech/np6gm38.html

在code裡面,我在原本的基礎上面,增加了一些註解,給大家參考可以怎麼修改這組code,以下code是用在只有一個分頁的時候,如果有多個分頁且需要指定分頁,可能需要再多增加指定分頁的語法,這部分可以參考第二篇有指定分頁的寫法。

看完這組code的註解,你可以學習到以下資訊。

1.如何計算某一欄有資料的最大列數?

2.如果要將C9欄的9寫成變數(i),要寫成Range(“C” & i),不能寫成Range(“C & i”)或Range(“C” & “i”),底下範例 i 寫為 rown

3.動態設定行高(列高)的方法

4.選取某合併儲存格到最後一行(列)的方法,如要從合併儲存格”CDE”第九行(列)一值選到最後一行(列),可以寫成Range(“C9:E9”, Range(“C9:E9”).End(xlDown)).Select

5.欄位沒有自動換列時,如何利用語法進行自動換列?

第四點的補充,是我在google搜尋了「range選取到最後一列」,找到的一個相關資源。

Range.End 屬性 (Excel)

原文網址:https://docs.microsoft.com/zh-tw/office/vba/api/excel.range.end

Sub 動態設置行高()

Dim rown As Integer
Dim high As Integer
Dim charlen As Integer
Dim Lrow As Long
Dim szs As Integer
Dim zms As Integer
Lrow = Range("A" & Cells.Rows.Count).End(xlUp).Row

'上面都是前置設定,注意Lrow那邊就算是從第九列才要開始要進行這個動態設置行高的語法。
'你也不需要將Lrow的結果做減9的動作,這裡的意思代表你A欄最後一筆資料位於的行數(列數)。

For rown = 9 To Lrow
'在rown的部分設定要進行此程式碼的起始行數(列數),我設定為第九行(列)
Set reg1 = CreateObject("VBSCRIPT.REGEXP")
Set reg2 = CreateObject("VBSCRIPT.REGEXP")

reg1.Pattern = "\C{1}"
'這部分語法我沒有很了解,但是因為我要設定是在第C欄,故改成C(我的合併儲存格是CDE欄)
reg2.Pattern = "[A-Za-z]{1}"
reg1.Global = True
reg2.Global = True

Set sz = reg1.Execute(Range("C" & rown))
Set zm = reg2.Execute(Range("C" & rown))
'這部分語法我沒有很了解,但是因為我要設定是在第C欄,故改成C(我的合併儲存格是CDE欄)

szs = sz.Count
zms = zm.Count

charlen = Len(Range("C" & rown)) - szs / 2 - zms / 2
'這部分語法我沒有很了解,但是因為我要設定是在第C欄,故改成C(我的合併儲存格是CDE欄)
high = Application.Round(charlen / 24, 0)
'24是我合併儲存格一欄的有幾個全形字,如果含標點符號是24就輸入24,如果是用英文和數字,英文和數字的個數就要除以二計算。
Rows(rown & ":" & rown).Select

'將那一行(列)整個選取
Selection.RowHeight = 20 + high * 16
'設定行高(列高),第一排是列高20,之後每一排是16,數字可以自行調整。
Next rown

'在迴圈完儲存格的部分之後,你會得到行高(列高)已經調整好了。
'但是你的資料可能並沒有進行自動換列,所以我們在以下做了自動換列的調整。
'為什麼不要在迴圈裡面一行一行做自動換列呢?因為會拖慢速度。

Range("C9:E9", Range("C9:E9").End(xlDown)).Select
'此範例為從C9到E9行(列)選取到最後一行(列)的語法
Selection.HorizontalAlignment = xlLeft
Selection.VerticalAlignment = xlTop
Selection.WrapText = True
'只有WrapText是自動換列的部分,其他項目是其他格式的設定,大家可以再自行研究。
Selection.Orientation = 0
Selection.AddIndent = False
Selection.IndentLevel = 0
Selection.ShrinkToFit = False
Selection.ReadingOrder = xlContext

End Sub

喜歡這篇文章,可以免費加入liker.land會員,並在底下為我拍手5下表達支持,會轉為實質收益,以利我支付網站營運費用,也歡迎留言告訴我你的看法,真心感謝你看完這篇文章。