การลบเซลล์ว่างออกจากช่วง

การกำหนดปัญหา

เรามีช่วงของเซลล์ที่มีข้อมูลที่มีเซลล์ว่าง:

ภารกิจคือการลบเซลล์ว่าง เหลือเพียงเซลล์ที่มีข้อมูล

วิธีที่ 1. หยาบและเร็ว

1. การเลือกช่วงเดิม
2. กดปุ่ม F5, ปุ่มถัดไป เน้น (พิเศษ). ในหน้าต่างที่เปิดขึ้น ให้เลือก เซลล์ว่าง(ช่องว่าง) และคลิก OK.

   

   เซลล์ว่างทั้งหมดในช่วงจะถูกเลือก

3. เราให้คำสั่งในเมนูเพื่อลบเซลล์ที่เลือก: คลิกขวา- ลบเซลล์ (ลบเซลล์) ด้วยการเลื่อนขึ้น

วิธีที่ 2: สูตรอาร์เรย์

เพื่อให้ง่ายขึ้น ให้ตั้งชื่อช่วงการทำงานของเราโดยใช้ ชื่อผู้จัดการ (ผู้จัดการชื่อ) แถบ สูตร (สูตร) หรือใน Excel 2003 ขึ้นไป เมนู แทรก – ชื่อ – มอบหมาย (แทรก — ชื่อ — กำหนด)

ตั้งชื่อช่วง B3:B10 มีที่ว่าง, ช่วง D3:D10 – ไม่มีว่าง. พิสัยต้องมีขนาดเท่ากันอย่างเคร่งครัด และสามารถระบุตำแหน่งที่สัมพันธ์กัน

ตอนนี้เลือกเซลล์แรกของช่วงที่สอง (D3) และป้อนสูตรที่น่ากลัวนี้ลงไป:

=IF(ROW() -ROW(NoEmpty)+1>NOTROWS(YesEmpty)-COUNTBLANK(YesEmpty);””;INDIRECT(ADDRESS(LOWEST((IF(ว่าง<>“”,ROW(ว่าง);ROW() + ROWS(มีว่าง))); LINE()-ROW(ไม่มีว่าง)+1); COLUMN(มีว่าง); 4)))

ในเวอร์ชันภาษาอังกฤษจะเป็น:

=IF(ROW()-ROW(NoEmpty)+1>ROWS(ว่าง)-COUNTBLANK(ว่าง),””,INDIRECT(ADDRESS(SMALL((IF(ว่าง<>“),ROW(ว่าง),ROW() +ROWS(มีว่าง))),ROW()-ROW(ไม่มีว่าง)+1),COLUMN(มีว่าง),4)))

ยิ่งกว่านั้นจะต้องป้อนเป็นสูตรอาร์เรย์เช่นกดหลังจากวาง เข้าสู่ (ตามปกติ) และ Ctrl + Shift + Enter. ตอนนี้สามารถคัดลอกสูตรได้โดยใช้การเติมข้อความอัตโนมัติ (ลากกากบาทสีดำที่มุมล่างขวาของเซลล์) - แล้วเราจะได้ช่วงดั้งเดิม แต่ไม่มีเซลล์ว่าง:

วิธีที่ 3 ฟังก์ชันที่กำหนดเองใน VBA

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

ในการดำเนินการนี้ ให้เปิด Visual Basic Editor (ALT + F11) ใส่โมดูลเปล่าใหม่ (เมนู แทรก – โมดูล) และคัดลอกข้อความของฟังก์ชันนี้ที่นั่น:

Function NoBlanks (DataRange As Range) As Variant() Dim N As Long Dim N2 As Long Dim Rng As Long Dim MaxCells As Long Dim Result() As Variant Dim R As Long Dim C As Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) ผลลัพธ์ ReDim (1 ถึง MaxCells, 1 ถึง 1) สำหรับแต่ละ Rng ใน DataRange.Cells ถ้า Rng.Value <> vbNullString จากนั้น N = N + 1 ผลลัพธ์ (N, 1 ) = Rng.Value End หาก Rng ถัดไปสำหรับ N2 = N + 1 ถึง MaxCells ผลลัพธ์ (N2, 1) = vbNullString N2 ถัดไป หาก Application.Caller.Rows.Count = 1 ดังนั้น NoBlanks = Application.Transpose (ผลลัพธ์) อื่น NoBlanks = ผลลัพธ์ สิ้นสุดถ้าสิ้นสุดฟังก์ชัน  

อย่าลืมบันทึกไฟล์และเปลี่ยนกลับจาก Visual Basic Editor เป็น Excel ในการใช้ฟังก์ชันนี้ในตัวอย่างของเรา:

1. เลือกช่วงเซลล์ว่างที่เพียงพอ เช่น F3:F10
2. ไปที่เมนู แทรก – ฟังก์ชัน (แทรก — ฟังก์ชัน)หรือคลิกที่ปุ่ม ฟังก์ชั่นแทรก (ฟังก์ชันแทรก) แถบ สูตร (สูตร) ใน Excel เวอร์ชันใหม่กว่า ในหมวด กำหนดโดยผู้ใช้ (กำหนดโดยผู้ใช้) เลือกฟังก์ชั่นของเรา Noblanks.
3. ระบุช่วงต้นทางด้วยช่องว่าง (B3:B10) เป็นอาร์กิวเมนต์ของฟังก์ชันและกด Ctrl + Shift + Enterเพื่อป้อนฟังก์ชันเป็นสูตรอาร์เรย์

:

• การลบแถวว่างทั้งหมดในตารางพร้อมกันด้วยมาโครอย่างง่าย
• การลบแถวว่างทั้งหมดในแผ่นงานพร้อมกันโดยใช้ PLEX add-on
• เติมเซลล์ว่างทั้งหมดอย่างรวดเร็ว
• มาโครคืออะไรที่จะแทรกโค้ดแมโครใน VBA