24 ตัวอย่าง Excel Macro ที่มีประโยชน์สำหรับผู้เริ่มต้น VBA (พร้อมใช้งาน)

การใช้มาโคร Excel ช่วยเพิ่มความเร็วในการทำงานและประหยัดเวลาได้มาก

วิธีหนึ่งในการรับโค้ด VBA คือการบันทึกมาโครและรับโค้ดที่สร้างขึ้นอย่างไรก็ตาม โค้ดของเครื่องบันทึกแมโครมักเต็มไปด้วยโค้ดที่ไม่จำเป็นจริงๆตัวบันทึกแมโครยังมีข้อจำกัดบางประการ

ดังนั้นจึงควรมีชุดมาโคร VBA ที่มีประโยชน์ซึ่งคุณสามารถเก็บไว้ในกระเป๋าหลังและใช้งานได้เมื่อคุณต้องการ

แม้ว่าอาจต้องใช้เวลาสักระยะในการเขียนโค้ดมาโคร Excel VBA ในขั้นต้น แต่เมื่อเสร็จแล้ว คุณสามารถใช้เป็นข้อมูลอ้างอิงและใช้ในครั้งต่อไปที่ต้องการได้

ในบทความขนาดใหญ่นี้ ฉันจะแสดงรายการตัวอย่างแมโคร Excel ที่มีประโยชน์ซึ่งฉันมักต้องการและเก็บไว้ในห้องนิรภัยส่วนตัว

ฉันจะอัปเดตบทช่วยสอนนี้ต่อไปพร้อมตัวอย่างมาโครเพิ่มเติมโปรดแสดงความคิดเห็นหากคุณคิดว่ามีบางอย่างอยู่ในรายการ

คุณสามารถคั่นหน้านี้เพื่อใช้อ้างอิงในอนาคต

ก่อนที่ฉันจะเข้าไปในตัวอย่างมาโครและให้โค้ด VBA แก่คุณ ให้ฉันแสดงวิธีใช้โค้ดตัวอย่างก่อน

การใช้โค้ดจากตัวอย่างมาโครของ Excel

ต่อไปนี้คือขั้นตอนที่ต้องปฏิบัติตามเพื่อใช้โค้ดจากตัวอย่างใดๆ:

• เปิดเวิร์กบุ๊กที่คุณต้องการใช้แมโคร
• กดปุ่ม ALT ค้างไว้แล้วกด F11จะเปิดขึ้นบรรณาธิการ VB.
• คลิกขวาที่วัตถุใดๆ ใน Project Explorer
• ไปที่แทรก -> โมดูล
• คัดลอกและวางรหัสในหน้าต่างรหัสโมดูล

ถ้าตัวอย่างบอกว่าคุณจำเป็นต้องวางโค้ดลงในหน้าต่างโค้ดของเวิร์กชีต ให้ดับเบิลคลิกที่ออบเจกต์ของเวิร์กชีตและคัดลอกและวางโค้ดลงในหน้าต่างโค้ด

หลังจากแทรกโค้ดลงในเวิร์กบุ๊กแล้ว คุณต้องบันทึกด้วยนามสกุล .XLSM หรือ .XLS

วิธีเรียกใช้แมโคร

หลังจากคัดลอกโค้ดในตัวแก้ไข VB แล้ว ขั้นตอนในการเรียกใช้แมโครจะเป็นดังนี้:

• ไปที่แท็บนักพัฒนา
• คลิกมาโคร

• ในกล่องโต้ตอบมาโคร ให้เลือกแมโครที่จะเรียกใช้
• คลิกปุ่มเรียกใช้

หากคุณไม่พบแท็บนักพัฒนาซอฟต์แวร์ใน Ribbon ให้อ่านบทช่วยสอนนี้เพื่อเรียนรู้วิธีใช้งาน

ถ้าโค้ดถูกวางในหน้าต่างโค้ดของเวิร์กชีต คุณไม่จำเป็นต้องกังวลเกี่ยวกับการรันโค้ดมันทำงานโดยอัตโนมัติเมื่อเกิดการกระทำที่ระบุ

ตอนนี้ มาเข้าสู่ตัวอย่างมาโครที่มีประโยชน์ซึ่งจะช่วยให้คุณทำงานอัตโนมัติและประหยัดเวลา

หมายเหตุ: คุณจะพบเครื่องหมายอะพอสทรอฟี (') จำนวนมากตามด้วยหนึ่งหรือสองบรรทัดความคิดเห็นเหล่านี้จะถูกละเว้นเมื่อเรียกใช้โค้ดและวางไว้เป็นความคิดเห็นของผู้อ่านเอง/ผู้อ่าน

หากคุณพบข้อผิดพลาดใด ๆ ในบทความหรือรหัส โปรดแจ้งให้เราทราบ

ตัวอย่างมาโคร Excel

บทความนี้อธิบายตัวอย่างแมโครต่อไปนี้:

เลิกซ่อนแผ่นงานทั้งหมดในครั้งเดียว

หากคุณกำลังทำงานในเวิร์กบุ๊กที่มีแผ่นงานที่ซ่อนอยู่หลายแผ่น คุณต้องเลิกซ่อนแผ่นงานเหล่านี้ทีละแผ่นอาจใช้เวลาสักครู่หากมีแผ่นงานที่ซ่อนอยู่จำนวนมาก

นี่คือรหัสเพื่อเลิกซ่อนแผ่นงานทั้งหมดในสมุดงาน

'รหัสนี้จะเลิกซ่อนแผ่นงานทั้งหมดในสมุดงานย่อย UnhideAllWoksheets() Dim ws As Worksheet For Each ws In ActiveWorkbook Worksheets ws. Visible = xlSheetVisible Next ws End Sub

โค้ดด้านบนใช้ VBA loop (สำหรับแต่ละรายการ) เพื่อวนซ้ำผ่านแต่ละแผ่นงานในสมุดงานจากนั้นจะเปลี่ยนคุณสมบัติที่มองเห็นได้ของแผ่นงานเป็นแบบมองเห็นได้

ต่อไปนี้คือบทช่วยสอนโดยละเอียดเกี่ยวกับวิธียกเลิกการซ่อนเวิร์กชีตใน Excel โดยใช้วิธีการต่างๆ

ซ่อนแผ่นงานทั้งหมดยกเว้นแผ่นงานที่ใช้งานอยู่

รหัสแมโครนี้สามารถใช้ได้หากคุณกำลังทำงานในรายงานหรือแดชบอร์ด และต้องการซ่อนแผ่นงานทั้งหมด ยกเว้นแผ่นงานที่มีรายงาน/แดชบอร์ด

'มาโครนี้จะซ่อนแผ่นงานทั้งหมดยกเว้นแผ่นงานย่อย HideAllExceptActiveSheet() Dim ws As Worksheet For Each ws ใน ThisWorkbook.Worksheets ถ้า ws.Name <> ActiveSheet.Name แล้ว ws.Visible = xlSheetHidden Next ws End Sub

จัดเรียงแผ่นงานตามตัวอักษรโดยใช้ VBA

รหัสมาโครนี้มีประโยชน์หากคุณมีเวิร์กบุ๊กที่มีแผ่นงานหลายแผ่น และคุณต้องการจัดเรียงแผ่นงานเหล่านั้นตามตัวอักษรสิ่งนี้สามารถเกิดขึ้นได้ถ้าคุณมีชื่อแผ่นงานเป็นชื่อปีหรือชื่อพนักงานหรือชื่อผลิตภัณฑ์

'รหัสนี้จะจัดเรียงแผ่นงานตามตัวอักษร Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount ถ้า Sheets(j).Name < Sheets(i).Name แล้วก็ Sheets(j).Move before:= Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub

ปกป้องแผ่นงานทั้งหมดในครั้งเดียว

หากคุณมีแผ่นงานจำนวนมากในสมุดงานและต้องการปกป้องแผ่นงานทั้งหมด คุณสามารถใช้โค้ดมาโครนี้ได้

ช่วยให้คุณสามารถระบุรหัสผ่านในรหัสคุณจะต้องใช้รหัสผ่านนี้เพื่อยกเลิกการป้องกันแผ่นงาน

'รหัสนี้จะปกป้องแผ่นงานทั้งหมดในครั้งเดียว Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'แทนที่ Test123 ด้วยรหัสผ่านที่คุณต้องการ For Each ws In Worksheets ws.Protect password:=password Next ws จบซับ

เลิกป้องกันแผ่นงานทั้งหมดในครั้งเดียว

ถ้าคุณป้องกันเวิร์กชีตของคุณบางส่วนหรือทั้งหมด คุณสามารถยกเลิกการป้องกันได้ด้วยการแก้ไขเพียงเล็กน้อยในโค้ดที่ใช้ป้องกันเวิร์กชีต

'รหัสนี้จะปกป้องแผ่นงานทั้งหมดในครั้งเดียว Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'แทนที่ Test123 ด้วยรหัสผ่านที่คุณต้องการ For Each ws In Worksheets ws.Unprotect password:=password Next ws จบซับ

โปรดทราบว่ารหัสผ่านจะต้องเป็นรหัสผ่านเดียวกันกับที่ใช้ในการล็อคแผ่นงานถ้าไม่ คุณจะเห็นข้อผิดพลาด

เลิกซ่อนแถวและคอลัมน์ทั้งหมด

รหัสมาโครนี้จะเลิกซ่อนแถวและคอลัมน์ที่ซ่อนอยู่ทั้งหมด

สิ่งนี้มีประโยชน์หากคุณนำไฟล์มาจากบุคคลอื่นและต้องการให้แน่ใจว่าไม่มีแถว/คอลัมน์ที่ซ่อนอยู่

'รหัสนี้จะยกเลิกการซ่อนแถวและคอลัมน์ทั้งหมดในแผ่นงานย่อย UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

ยกเลิกการผสานเซลล์ที่ผสานทั้งหมด

การรวมเซลล์เข้าด้วยกันเป็นแนวทางปฏิบัติทั่วไปขณะที่ทำงาน คุณจะไม่สามารถเรียงลำดับข้อมูลเมื่อเซลล์ถูกผสาน

หากคุณกำลังใช้เวิร์กชีตที่มีเซลล์ที่ผสาน ให้ใช้โค้ดด้านล่างเพื่อยกเลิกการผสานเซลล์ที่ผสานทั้งหมดในคราวเดียว

'รหัสนี้จะยกเลิกการผสานเซลล์ที่ผสานทั้งหมด Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

โปรดทราบว่าฉันแนะนำให้ใช้ตัวเลือก "กึ่งกลางระหว่างส่วนที่เลือก" แทนตัวเลือก "ผสานและอยู่ตรงกลาง"

บันทึกสมุดงานด้วยการประทับเวลาในชื่อ

มีหลายครั้งที่คุณอาจต้องสร้างเวอร์ชันที่ใช้งานได้สิ่งเหล่านี้ยอดเยี่ยมสำหรับโครงการระยะยาวที่เกี่ยวข้องกับไฟล์เมื่อเวลาผ่านไป

แนวปฏิบัติที่ดีคือการบันทึกไฟล์ด้วยการประทับเวลา

การใช้การประทับเวลาจะทำให้คุณสามารถกลับไปที่ไฟล์เพื่อดูว่ามีการเปลี่ยนแปลงใดบ้างหรือมีการใช้ข้อมูลใด

นี่คือรหัสที่จะบันทึกเวิร์กบุ๊กในโฟลเดอร์ที่ระบุโดยอัตโนมัติและเพิ่มการประทับเวลาเมื่อบันทึก

'รหัสนี้จะบันทึกไฟล์ด้วย Timestamp ในชื่อ Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & การประทับเวลา End Sub

คุณต้องระบุตำแหน่งโฟลเดอร์และชื่อไฟล์

ในโค้ดด้านบน "C:UsersUsernameDesktop คือตำแหน่งโฟลเดอร์ที่ฉันใช้คุณต้องระบุตำแหน่งโฟลเดอร์ที่คุณต้องการบันทึกไฟล์นอกจากนี้ ฉันยังใช้ชื่อทั่วไปว่า "WorkbookName" เป็นคำนำหน้าชื่อไฟล์คุณสามารถระบุเนื้อหาที่เกี่ยวข้องกับโครงการหรือบริษัทของคุณ

บันทึกแผ่นงานแต่ละแผ่นเป็น PDF แยกกัน

หากคุณใช้ข้อมูลจากปีหรือแผนกหรือผลิตภัณฑ์ที่แตกต่างกัน คุณอาจต้องบันทึกเวิร์กชีตอื่นเป็นไฟล์ PDF

แม้ว่าขั้นตอนนี้อาจเป็นกระบวนการที่ใช้เวลานานหากทำด้วยตนเอง แต่ VBA จะทำให้สิ่งต่างๆ เร็วขึ้น

นี่คือรหัส VBA ที่บันทึกแต่ละแผ่นงานเป็น PDF แยกต่างหาก

'รหัสนี้จะบันทึกแต่ละแผ่นงานเป็นไฟล์ PDF ย่อยแยกกัน SaveWorkshetAsPDF() Dim ws As Worksheet For Each ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub

ในโค้ดด้านบนนี้ ฉันได้ระบุที่อยู่ของตำแหน่งโฟลเดอร์ที่ฉันต้องการบันทึก PDFนอกจากนี้ แต่ละ PDF จะได้รับชื่อเดียวกันกับแผ่นงานคุณจะต้องแก้ไขตำแหน่งโฟลเดอร์นี้ (เว้นแต่ชื่อของคุณคือ Sumit และคุณบันทึกไว้ในโฟลเดอร์ทดสอบบนเดสก์ท็อปของคุณ)

โปรดทราบว่าโค้ดนี้ใช้ได้เฉพาะกับเวิร์กชีตเท่านั้น (ใช้ไม่ได้กับแผ่นงานแผนภูมิ)

บันทึกแผ่นงานแต่ละแผ่นเป็น PDF แยกกัน

นี่คือรหัสสำหรับบันทึกสมุดงานทั้งหมดเป็น PDF ในโฟลเดอร์ที่ระบุ

'รหัสนี้จะบันทึกเวิร์กบุ๊กทั้งหมดเป็น PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub

คุณต้องเปลี่ยนตำแหน่งโฟลเดอร์เพื่อใช้รหัสนี้

แปลงสูตรทั้งหมดเป็นค่า

ใช้รหัสนี้เมื่อคุณมีเวิร์กชีตที่มีสูตรจำนวนมาก และคุณต้องการแปลงสูตรเหล่านี้เป็นค่า

'รหัสนี้จะแปลงสูตรทั้งหมดเป็นค่า Sub ConvertToValues() ด้วย ActiveSheet.UsedRange .Value = .Value End With End Sub

รหัสนี้จะจดจำเซลล์ที่ใช้โดยอัตโนมัติและแปลงเป็นค่า

ปกป้อง/ล็อคเซลล์ด้วยสูตร

เมื่อคุณมีการคำนวณจำนวนมากและไม่ต้องการลบหรือเปลี่ยนแปลงโดยไม่ได้ตั้งใจ คุณอาจต้องการล็อกเซลล์ด้วยสูตร

นี่คือรหัสที่จะล็อกเซลล์ทั้งหมดที่มีสูตรและปล่อยให้เซลล์อื่นๆ ปลดล็อกอยู่

'รหัสมาโครนี้จะล็อกเซลล์ทั้งหมดที่มีสูตรย่อย LockCellsWithFormulas() ด้วย ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub

บทช่วยสอนที่เกี่ยวข้อง: วิธีล็อคเซลล์ใน Excel

ป้องกันแผ่นงานทั้งหมดในสมุดงาน

ใช้รหัสต่อไปนี้เพื่อป้องกันแผ่นงานทั้งหมดในสมุดงานพร้อมกัน

'รหัสนี้จะปกป้องแผ่นงานทั้งหมดในสมุดงาน Sub ProtectAllSheets() Dim ws As Worksheet For Each ws In Worksheets ws. Protect Next ws End Sub

รหัสนี้จะผ่านแผ่นงานทั้งหมดทีละแผ่นและปกป้องแผ่นงาน

หากคุณต้องการยกเลิกการป้องกันแผ่นงานทั้งหมด ให้ใช้ ws.Unprotect แทน ws.Protect ในโค้ดของคุณ

แทรกแถวหลังแต่ละแถวของส่วนที่เลือก

ใช้รหัสนี้เมื่อคุณต้องการแทรกแถวว่างหลังทุกแถวในช่วงที่เลือก

'รหัสนี้จะแทรกแถวหลังทุกแถวในการเลือก Sub InsertAlternateRows() Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow แทรก ActiveCell.Offset(2, 0).Select Next i End Sub

อีกครั้ง คุณสามารถแก้ไขโค้ดนี้เพื่อแทรกคอลัมน์ว่างหลังแต่ละคอลัมน์ในช่วงที่เลือกได้

แทรกวันที่และเวลาในเซลล์ที่อยู่ติดกันโดยอัตโนมัติ

การประทับเวลาคือสิ่งที่คุณใช้เมื่อคุณต้องการติดตามกิจกรรม

ตัวอย่างเช่น คุณอาจต้องการติดตามกิจกรรม เช่น เมื่อมีค่าใช้จ่ายเฉพาะ เมื่อสร้างอินวอยซ์การขาย เมื่อข้อมูลถูกป้อนในเซลล์ เมื่อใดที่รายงานได้รับการปรับปรุงล่าสุด และอื่นๆ

ใช้รหัสนี้เพื่อแทรกการประทับวันที่และเวลาในเซลล์ที่อยู่ติดกันเมื่อพิมพ์หรือแก้ไขเนื้อหาที่มีอยู่

'รหัสนี้จะแทรกการประทับเวลาในเซลล์ที่อยู่ติดกัน Private Sub Worksheet_Change (ByVal Target As Range) บนข้อผิดพลาด GoTo Handler ถ้า Target.Column = 1 และ Target.Value <> "" จากนั้น Application.EnableEvents = False Target.Offset (0, 1) = รูปแบบ (ตอนนี้ (), "dd-mm-yyyy hh:mm:ss") Application.EnableEvents = True End หากตัวจัดการ: End Sub

โปรดทราบว่าคุณต้องแทรกโค้ดนี้ในหน้าต่างโค้ดของเวิร์กชีต (ไม่ใช่ในหน้าต่างโค้ดโมดูล เหมือนที่เราทำในตัวอย่างแมโคร Excel อื่นๆ จนถึงตอนนี้)เมื่อต้องการทำเช่นนี้ ในตัวแก้ไข VB ให้ดับเบิลคลิกที่ชื่อแผ่นงานที่คุณต้องการให้ฟังก์ชันนี้จากนั้นคัดลอกและวางรหัสนี้ลงในหน้าต่างรหัสของแผ่นงานนั้น

นอกจากนี้ โค้ดนี้ใช้งานได้เมื่อป้อนข้อมูลในคอลัมน์ A (โปรดทราบว่าโค้ดมี Target.Column = 1 บรรทัด)คุณสามารถเปลี่ยนได้ตามต้องการ

เน้นแถวอื่นในส่วนที่เลือก

การเน้นบรรทัดสลับกันสามารถปรับปรุงความสามารถในการอ่านข้อมูลของคุณได้อย่างมากซึ่งจะมีประโยชน์เมื่อคุณต้องการพิมพ์และเรียกดูข้อมูล

นี่คือรหัสที่จะไฮไลต์แถวสำรองในส่วนที่เลือกทันที

'รหัสนี้จะเน้นแถวทางเลือกในการเลือก Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection For Each Myrow In Myrange.Rows ถ้า Myrow.Row Mod 2 = 1 จากนั้น Myrow.Interior.Color = vbCyan End ถ้า Next Myrow End Sub

โปรดทราบว่าฉันระบุสีเป็น vbCyan ในโค้ดคุณยังสามารถระบุสีอื่นๆ (เช่น vbRed, vbGreen, vbBlue)

เน้นเซลล์ที่สะกดผิด

Excel ไม่มีการตรวจตัวสะกดใน Word หรือ PowerPointขณะที่คุณสามารถเรียกใช้การตรวจตัวสะกดได้โดยการกด F7 จะไม่มีสัญลักษณ์บ่งชี้ข้อผิดพลาดในการสะกดคำ

ใช้รหัสนี้เพื่อเน้นเซลล์ที่สะกดผิดทั้งหมดทันที

'รหัสนี้จะเน้นเซลล์ที่สะกดคำผิด Sub HighlightMisspelledCells() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) จากนั้น cl.Interior.Color = vbRed End If Next cl End Sub

โปรดทราบว่าเซลล์ที่ไฮไลต์คือเซลล์ที่มีข้อความที่ Excel คิดว่าสะกดผิดในหลายกรณี ยังเน้นชื่อหรือคำศัพท์ของแบรนด์ที่ไม่เข้าใจ

รีเฟรชตารางสาระสำคัญทั้งหมดในสมุดงาน

หากคุณมีตาราง Pivot หลายตารางในเวิร์กบุ๊ก คุณสามารถใช้รหัสนี้เพื่อรีเฟรชตารางทั้งหมดพร้อมกันได้

'รหัสนี้จะรีเฟรช Pivot Table ทั้งหมดในเวิร์กบุ๊กย่อย RefreshAllPivotTables() Dim PT เป็น PivotTable สำหรับแต่ละ PT ใน ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub

คุณสามารถอ่านเพิ่มเติมเกี่ยวกับการรีเฟรชตารางสาระสำคัญได้ที่นี่

เปลี่ยนตัวพิมพ์ของเซลล์ที่เลือกเป็นตัวพิมพ์ใหญ่

แม้ว่า Excel จะมีสูตรสำหรับเปลี่ยนตัวพิมพ์ของตัวอักษรข้อความ แต่ก็ให้คุณทำสิ่งนี้ได้ในเซลล์ชุดอื่น

ใช้รหัสนี้เพื่อเปลี่ยนตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ของข้อความในข้อความที่เลือกทันที

'รหัสนี้จะเปลี่ยนการเลือกเป็นตัวพิมพ์ใหญ่ย่อย ChangeCase() Dim Rng เป็นช่วงสำหรับแต่ละ Rng ใน Selection.Cells ถ้า Rng.HasFormula = False จากนั้น Rng.Value = UCase (Rng.Value) สิ้นสุดหาก Next Rng End Sub

โปรดทราบว่าในกรณีนี้ ฉันใช้ Ucase เพื่อตั้งค่าตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ให้เป็นตัวพิมพ์ใหญ่คุณสามารถใช้ LCase สำหรับตัวพิมพ์เล็ก

เน้นทุกเซลล์ด้วยความคิดเห็น

ใช้รหัสต่อไปนี้เพื่อเน้นเซลล์ทั้งหมดที่มีความคิดเห็นอยู่ในนั้น

'รหัสนี้จะเน้นเซลล์ที่มีความคิดเห็น' Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

ในกรณีนี้ ฉันใช้ vbBlue เพื่อให้เซลล์เป็นสีน้ำเงินคุณสามารถเปลี่ยนเป็นสีอื่นได้หากต้องการ

เน้นเซลล์ว่างด้วย VBA

แม้ว่าคุณจะใช้การจัดรูปแบบตามเงื่อนไขหรือใช้กล่องโต้ตอบไปที่แบบพิเศษเพื่อไฮไลต์เซลล์ว่างได้ แต่หากคุณจำเป็นต้องทำเช่นนี้บ่อยๆ ควรใช้มาโคร

เมื่อสร้างแล้ว คุณสามารถมีแมโครนี้ในแถบเครื่องมือด่วนหรือบันทึกไว้ในเวิร์กบุ๊กแมโครส่วนบุคคลของคุณ

นี่คือรหัสมาโคร VBA:

'รหัสนี้จะเน้นเซลล์ว่างทั้งหมดในชุดข้อมูลย่อย HighlightBlankCells() Dim Dataset เป็นช่วง Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

ในรหัสนี้ ฉันระบุเซลล์ว่างที่เน้นสีแดงคุณสามารถเลือกสีอื่น ๆ เช่น สีฟ้า สีเหลือง สีฟ้า ฯลฯ.

วิธีจัดเรียงข้อมูลตามคอลัมน์เดียว

คุณสามารถใช้รหัสต่อไปนี้เพื่อจัดเรียงข้อมูลตามคอลัมน์ที่ระบุ

ย่อย SortDataHeader() 
ช่วง("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes 
ย่อยสิ้นสุด

โปรดทราบว่าฉันสร้างช่วงที่มีชื่อเรียกว่า "DataRange" และใช้ช่วงนั้นแทนการอ้างอิงเซลล์

นอกจากนี้ยังมีพารามิเตอร์หลักสามตัวที่ใช้ที่นี่:

• Key1 – นี่คือคีย์ที่คุณต้องการจัดเรียงชุดข้อมูลในโค้ดตัวอย่างด้านบน ข้อมูลจะถูกจัดเรียงตามค่าในคอลัมน์ A
• ลำดับที่ 1 – ที่นี่คุณต้องระบุว่าคุณต้องการเรียงลำดับข้อมูลในลำดับจากน้อยไปมากหรือมากไปหาน้อย
• ส่วนหัว – ที่นี่คุณต้องระบุว่าข้อมูลของคุณมีส่วนหัวหรือไม่

อ่านเพิ่มเติมเกี่ยวกับวิธีการจัดเรียงข้อมูลใน Excel โดยใช้ VBA

วิธีจัดเรียงข้อมูลตามหลายคอลัมน์

สมมติว่าคุณมีชุดข้อมูลที่มีลักษณะดังนี้:

นี่คือรหัสสำหรับจัดเรียงข้อมูลตามหลายคอลัมน์:

Sub SortMultipleColumns() ด้วย ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Header = xlYes .Apply End With End Sub

โปรดทราบว่าที่นี่ฉันได้ระบุให้เรียงลำดับในคอลัมน์ A ก่อนแล้วจึงในคอลัมน์ B

ผลลัพธ์จะมีลักษณะดังนี้:

วิธีรับเฉพาะส่วนตัวเลขจากสตริงใน Excel

หากคุณต้องการแยกส่วนตัวเลขหรือส่วนข้อความออกจากสตริง คุณสามารถสร้างฟังก์ชันแบบกำหนดเองใน VBA ได้

จากนั้น คุณสามารถใช้ฟังก์ชัน VBA นี้ในเวิร์กชีตของคุณ (เช่นเดียวกับฟังก์ชัน Excel ปกติ) และจะดึงเฉพาะตัวเลขหรือส่วนข้อความออกจากสตริง

ดังที่แสดงด้านล่าง:

นี่คือโค้ด VBA ที่จะสร้างฟังก์ชันที่แยกส่วนที่เป็นตัวเลขออกจากสตริง:

'โค้ด VBA นี้จะสร้างฟังก์ชันเพื่อรับส่วนตัวเลขจากสตริง Function GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1) ) จากนั้น Result = Result & Mid (CellRef, i, 1) Next i GetNumeric = ฟังก์ชันสิ้นสุดผลลัพธ์

คุณต้องใส่รหัสในโมดูลแล้วจึงสามารถใช้ฟังก์ชัน =GetNumeric ในเวิร์กชีตได้

ฟังก์ชันนี้จะใช้พารามิเตอร์เพียงตัวเดียว การอ้างอิงเซลล์ของเซลล์ที่จะรับส่วนที่เป็นตัวเลข

ในทำนองเดียวกัน ฟังก์ชันด้านล่างจะได้รับเฉพาะส่วนข้อความจากสตริงใน Excel:

'โค้ด VBA นี้จะสร้างฟังก์ชันเพื่อรับส่วนข้อความจากสตริง Function GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) จากนั้น Result = Result & Mid (CellRef, i, 1) Next i GetText = ฟังก์ชันสิ้นสุดผลลัพธ์

ดังนั้น นี่คือรหัสมาโคร Excel ที่มีประโยชน์ซึ่งคุณสามารถใช้ในการทำงานประจำวันของคุณเพื่อทำให้งานเป็นอัตโนมัติและเพิ่มประสิทธิภาพการทำงานของคุณ