ช่วยแนะนำสูตรการลบแถวที่มีค่า=0 แบบอัตโนมัติหน่อยครับ

[pro602]

เรียนพี่ๆสมาชิกและอาจารย์ทุกท่าน
ตาม File ที่ผมแนบมารบกวนช่วยแนะนำการลบข้อมูลทั้งแถวเมื่อค่าใน Column D=0 ให้หน่อยครับ (แบบกดปุ่มหรือคำสั่งและให้ลบแบบอัตโนมัติครับ)รายละเอียดที่ต้องการจะชี้แจงอยู่ใน Sheet ครับ
-ขอบคุณครับ

[puriwutpokin]

การลบแบบนั้น โดยใช้ข้อมูลในชุดเดียวกันที่ลบ ต้องใช้ VBA ลอง ค้นหาจาก Google ในหัวข้อเกี่ยวกับ VBA Delete Row
แล้วลองทดสอบดู ติดตรงไหนมาถามกันในนี้ได้ครับ

[snasui]

Filter ก่อนแล้วค่อยลบครับ

ขั้นตอน

1. คลิกเซลล์ A3 > เข้าเมนู Data > Filter
2. คลิก Drowdown ที่ A3 > ปลดเครื่องหมายที่ (Select All) แล้วเลือก (Blanks)
3. คลิก Drowdown ที่ D3 > ปลดเครื่องหมายที่ (Select All) แล้วเลือก 0
4. ลากเมาส์คลุม A5:An (n คือเซลล์สุดท้ายที่มีข้อมูลจากผลลัพธ์จากการ Filter) > กดแป้น F5 > Special > Visible cells only > OK
5. คลิกขวาผลลัพธ์ที่ได้ > Delete row > OK
6. เข้าเมนู Data > Clear > Filter เพื่อปลดการ Filter และยกเลิก Auto Filter

อ้อ...อันนี้ไม่ได้อัตโนมัตินะครับ ถ้าจะอัตโนมัติก็สามารถนำขั้นตอนนี้ไป Record Macro อีกที ไม่เช่นนั้นก็ต้องใช้ VBA ครับ

[pro602]

Filter ก่อนแล้วค่อยลบครับ

ขั้นตอน

1. คลิกเซลล์ A3 > เข้าเมนู Data > Filter
2. คลิก Drowdown ที่ A3 > ปลดเครื่องหมายที่ (Select All) แล้วเลือก (Blanks)
3. คลิก Drowdown ที่ D3 > ปลดเครื่องหมายที่ (Select All) แล้วเลือก 0
4. ลากเมาส์คลุม A5:An (n คือเซลล์สุดท้ายที่มีข้อมูลจากผลลัพธ์จากการ Filter) > กดแป้น F5 > Special > Visible cells only > OK
5. คลิกขวาผลลัพธ์ที่ได้ > Delete row > OK
6. เข้าเมนู Data > Clear > Filter เพื่อปลดการ Filter และยกเลิก Auto Filter

อ้อ...อันนี้ไม่ได้อัตโนมัตินะครับ ถ้าจะอัตโนมัติก็สามารถนำขั้นตอนนี้ไป Record Macro อีกที ไม่เช่นนั้นก็ต้องใช้ VBA ครับ

ผมลองทำามคำแนะนำอาจารย์แล้วครับ ผมก็ยังเจอปัญหาถ้าColumn D ของแถวแรกแต่ล่ะ Section มีค่า=0 ผลที่
ได้หลังจากFilterก็ยังคงมีค่า 0 อยู่ครับผม (ถ้าจะเขียนสูตรหรือคำสั่งให้มีการ Copy section ลงไปเรื่อยๆแบบอัตโนมัติต้องเขียนยังงัยครับ)

[snasui]

ความต้องการคือ ต้องการลบข้อมูลในคอลัมน์ B:D เท่านั้น คอลัมน์ A ไม่เกี่ยวข้องใช่หรือไม่ครับ

ตามที่ถามมาสามารถเขียนสูตรได้แต่จะต้องเข้าใจความต้องการชัดเจน ลองทำตัวอย่างมาสักเล็กน้อย ทำตัวอย่างคำตอบมาด้วย จะได้เข้าใจได้ทันทีครับ

[pro602]

ความต้องการคือ ต้องการลบข้อมูลในคอลัมน์ B:D เท่านั้น คอลัมน์ A ไม่เกี่ยวข้องใช่หรือไม่ครับ

ตามที่ถามมาสามารถเขียนสูตรได้แต่จะต้องเข้าใจความต้องการชัดเจน ลองทำตัวอย่างมาสักเล็กน้อย ทำตัวอย่างคำตอบมาด้วย จะได้เข้าใจได้ทันทีครับ

ผลที่ต้องการจะออกมาตาม "Sheet ผลที่ต้องการ" ตามFile แนบครับ

[snasui]

วิธีการทำให้เซลล์ว่างในคอลัมน์ A เหมือนกับค่าที่อยู่ด้านบนสามารถทำตามด้านล่างครับ

1. คลุม A4:A271 > กดแป้น F5 > Special > Blanks > OK
2. คีย์เครื่องหมาย = ตามด้วยกดปุ่มลูกศรชี้ขึ้นด้านบน > กดแป้น Ctrl ค้างไว้แล้วตามด้วย Enter จะเป็นการเติมค่าให้เหมือนกับค่าที่อยู่ด้านบน
3. คลิกขวาที่หัวคอลัมน์ A > Copy > คลิกขวาซ้ำอีกรอบ > Paste Special > Valus เพื่อวางให้เป็นค่าคงที่
4. ทำการ Filter โดยเลือกค่า 0 ในคอลัมน์ D แล้วลบทิ้งทั้งบรรทัดตามวิธีการในโพสต์ #3 ครับ

[pro602]

วิธีการทำให้เซลล์ว่างในคอลัมน์ A เหมือนกับค่าที่อยู่ด้านบนสามารถทำตามด้านล่างครับ

1. คลุม A4:A271 > กดแป้น F5 > Special > Blanks > OK
2. คีย์เครื่องหมาย = ตามด้วยกดปุ่มลูกศรชี้ขึ้นด้านบน > กดแป้น Ctrl ค้างไว้แล้วตามด้วย Enter จะเป็นการเติมค่าให้เหมือนกับค่าที่อยู่ด้านบน
3. คลิกขวาที่หัวคอลัมน์ A > Copy > คลิกขวาซ้ำอีกรอบ > Paste Special > Valus เพื่อวางให้เป็นค่าคงที่
4. ทำการ Filter โดยเลือกค่า 0 ในคอลัมน์ D แล้วลบทิ้งทั้งบรรทัดตามวิธีการในโพสต์ #3 ครับ

ขอบคุณครับอาจารย์

[pro602]

วิธีการทำให้เซลล์ว่างในคอลัมน์ A เหมือนกับค่าที่อยู่ด้านบนสามารถทำตามด้านล่างครับ

1. คลุม A4:A271 > กดแป้น F5 > Special > Blanks > OK
2. คีย์เครื่องหมาย = ตามด้วยกดปุ่มลูกศรชี้ขึ้นด้านบน > กดแป้น Ctrl ค้างไว้แล้วตามด้วย Enter จะเป็นการเติมค่าให้เหมือนกับค่าที่อยู่ด้านบน
3. คลิกขวาที่หัวคอลัมน์ A > Copy > คลิกขวาซ้ำอีกรอบ > Paste Special > Valus เพื่อวางให้เป็นค่าคงที่
4. ทำการ Filter โดยเลือกค่า 0 ในคอลัมน์ D แล้วลบทิ้งทั้งบรรทัดตามวิธีการในโพสต์ #3 ครับ

-ขอคำแนะนำเพิ่มเติมครับผม หลังจากที่ผมได้ทำตามคำแนะนำที่อาจารย์ได้แนะนำมาผลที่ได้เป็นไปตามที่ต้องการครับและผมได้ลองทำให้ทำงานแบบอัตโนมัติโดยการRecord Macro จะพบปัญหาในกรณีที่ช่วงข้อมูลเพิ่มมากกว่าเดิม รายงานที่ออกมาช่วงข้อมูลที่เพิ่มมาจะไม่เพิ่ม Sectionและลบ 0 ให้ครับ ช่วยแนะนำขั้นตอนหรือสูตรการแก้ไขให้มีการคิดคำนวนในกรณีมีช่วงข้อมูลเพิ่มเติมให้ผมหน่อยครับ

[puriwutpokin]

ลองปรับเป็นตามนี้ครับ

Code: Select all

Sub Macro1()
    Dim finalrow As Long
    Dim i As Long
    finalrow = Cells(Rows.Count, 2).End(xlUp).Row
    For i = finalrow To 4 Step -1
        If Cells(i, 4).Value = 0 And Cells(i, 1).Value = "" Then Cells(i, 1).EntireRow.Delete
    Next i
End Sub

[pro602]

ลองปรับเป็นตามนี้ครับ

Code: Select all

Sub Macro1()
    Dim finalrow As Long
    Dim i As Long
    finalrow = Cells(Rows.Count, 2).End(xlUp).Row
    For i = finalrow To 4 Step -1
        If Cells(i, 4).Value = 0 And Cells(i, 1).Value = "" Then Cells(i, 1).EntireRow.Delete
    Next i
End Sub

ช่วยแนะนำการนำ code ที่แนบมาให้ผมหน่อยครับต้องเอาไปใส่สูตรตรงไหนครับ ตัวอย่างที่ผมแนบมาให้ผมทำโดยการ Record Macro ถ้าจะปรับสูตรเพิ่มเติมช่วยแนะนำการเข้าไปปรับให้ผมหน่อยครับ

[puriwutpokin]

ลองปรับเป็นตามนี้ครับ

Code: Select all

Sub Macro1()
    Dim finalrow As Long
    Dim i As Long
    finalrow = Cells(Rows.Count, 2).End(xlUp).Row
    For i = finalrow To 4 Step -1
        If Cells(i, 4).Value = 0 And Cells(i, 1).Value = "" Then Cells(i, 1).EntireRow.Delete
    Next i
End Sub

ช่วยแนะนำการนำ code ที่แนบมาให้ผมหน่อยครับต้องเอาไปใส่สูตรตรงไหนครับ ตัวอย่างที่ผมแนบมาให้ผมทำโดยการ Record Macro ถ้าจะปรับสูตรเพิ่มเติมช่วยแนะนำการเข้าไปปรับให้ผมหน่อยครับ

ใส่แทนโค้ดเดิม ที่ Record ครับ

[pro602]

ลองปรับเป็นตามนี้ครับ

Code: Select all

Sub Macro1()
    Dim finalrow As Long
    Dim i As Long
    finalrow = Cells(Rows.Count, 2).End(xlUp).Row
    For i = finalrow To 4 Step -1
        If Cells(i, 4).Value = 0 And Cells(i, 1).Value = "" Then Cells(i, 1).EntireRow.Delete
    Next i
End Sub

ช่วยแนะนำการนำ code ที่แนบมาให้ผมหน่อยครับต้องเอาไปใส่สูตรตรงไหนครับ ตัวอย่างที่ผมแนบมาให้ผมทำโดยการ Record Macro ถ้าจะปรับสูตรเพิ่มเติมช่วยแนะนำการเข้าไปปรับให้ผมหน่อยครับ

ใส่แทนโค้ดเดิม ที่ Record ครับ

หลังจากผมได้ลองเอา Code ไปใส่แทน Code เดิม มันจะเป็นการลบแถวที่ Column D=0 เลย แต่จะยังคงค่า 0 ไว้หากในแถวนั้นที่ Column A มีชื่อ Section อยู่...อันนี้ผมเข้าใจถูกต้องไหมครับ
แต่ผลที่ผมอยากได้คือให้ลบทุกแถวเมื่อค่าที่ Column D=0 หรือค่าว่าง ถึงแม้ว่าในแถวนั้นๆที่ตำแหน่ง Section จะมีชื่ออยู่ก็ตามก็ให้ลบด้วยแต่ให้ชื่อ Section ขยับลงมาแสดงในช่วงแถวถัดไปที่มีค่าครับ ช่วยแนะนำผมหน่อครับ...ผมแนบตัวอย่างหลังจากใส่ Code ตามที่แนะนำครับ...ต้องขออภัยพี่ๆและอาจารย์ทุกท่านครับที่รบกวนทุกขั้นตอน ผมพึ่งจะเคยใช้งานเกี่ยวกับ Record macro และ VBA ครับ

[puriwutpokin]

ปรับตามนี้ครับ

Code: Select all

Sub Macro1()
    Dim finalrow As Long
    Dim i As Long
    finalrow = Cells(Rows.Count, 2).End(xlUp).Row
    For i = finalrow To 4 Step -1
        If Cells(i, 4).Value = 0 Then​
        Sub Macro1()
    Dim finalrow As Long
    Dim i As Long
    finalrow = Cells(Rows.Count, 2).End(xlUp).Row
    For i = finalrow To 4 Step -1
        If Cells(i, 4).Value = 0 And Cells(i, 1).Value = "" Then 
        Cells(i+1, 1).Value = Cells(i, 1).Value
        Cells(i, 1).EntireRow.Delete
    Next i
End Sub

[puriwutpokin]

ปรับตามนี้ครับ

Code: Select all

Sub Macro1()
    Dim finalrow As Long
    Dim i As Long
    finalrow = Cells(Rows.Count, 2).End(xlUp).Row
    For i = finalrow To 4 Step -1
        If Cells(i, 4).Value = 0 Then​
        Sub Macro1()
    Dim finalrow As Long
    Dim i As Long
    finalrow = Cells(Rows.Count, 2).End(xlUp).Row
    For i = finalrow To 4 Step -1
        If Cells(i, 4).Value = 0 And Cells(i, 1).Value = "" Then 
        Cells(i+1, 1).Value = Cells(i, 1).Value
        Cells(i, 1).EntireRow.Delete
    Next i
End Sub
[/quote]

แก้ไขครับ   :roll: 
[code]Sub Macro1()
    Dim finalrow As Long
    Dim i As Long
    finalrow = Cells(Rows.Count, 2).End(xlUp).Row
    For i = finalrow To 4 Step -1
        If Cells(i, 4).Value = 0 And Cells(i, 1).Value <> "" Then
        Cells(i + 1, 1).Value = Cells(i, 1).Value
        Cells(i, 1).EntireRow.Delete
        ElseIf Cells(i, 4).Value = 0 And Cells(i, 1).Value = "" Then
        Cells(i, 1).EntireRow.Delete
End If
    Next i
End Sub

[pro602]

ปรับตามนี้ครับ

Code: Select all

Sub Macro1()
    Dim finalrow As Long
    Dim i As Long
    finalrow = Cells(Rows.Count, 2).End(xlUp).Row
    For i = finalrow To 4 Step -1
        If Cells(i, 4).Value = 0 Then​
        Sub Macro1()
    Dim finalrow As Long
    Dim i As Long
    finalrow = Cells(Rows.Count, 2).End(xlUp).Row
    For i = finalrow To 4 Step -1
        If Cells(i, 4).Value = 0 And Cells(i, 1).Value = "" Then 
        Cells(i+1, 1).Value = Cells(i, 1).Value
        Cells(i, 1).EntireRow.Delete
    Next i
End Sub
[/quote]

แก้ไขครับ   :roll: 
[code]Sub Macro1()
    Dim finalrow As Long
    Dim i As Long
    finalrow = Cells(Rows.Count, 2).End(xlUp).Row
    For i = finalrow To 4 Step -1
        If Cells(i, 4).Value = 0 And Cells(i, 1).Value <> "" Then
        Cells(i + 1, 1).Value = Cells(i, 1).Value
        Cells(i, 1).EntireRow.Delete
        ElseIf Cells(i, 4).Value = 0 And Cells(i, 1).Value = "" Then
        Cells(i, 1).EntireRow.Delete
End If
    Next i
End Sub

-ขอบคุณครับท่าน"puriwutpokin"สำหรับ Code ที่ให้ผมมา ได้ผลลัพธ์ตรงตามที่ต้องการในหน้าเดียวเลยครับ ถ้าไม่เป็นการรบกวนท่าน"puriwutpokin" จะขอรบกวนท่านช่วยอธิบายความหมายใน Code เพื่อเป็นวิทยาทานให้ผมได้นำไปศึกษาและทำความเข้าใจต่อยอดในหลักการเขียน Code VBA ตามที่ท่านได้แนะนำมาให้กับผมหน่อยครับ....ซึ่ง Step ต่อไปผมก็เขียนรายงานสรุปจ่ายวัสดุแยกออกเป็นแต่ล่ะ Section ต่อไปครับ
sub Macro1()
Dim finalrow As Long....หมายถึง
Dim i As Long....หมายถึง
finalrow = Cells(Rows.Count, 2).End(xlUp).Row....หมายถึง
For i = finalrow To 4 Step -1....หมายถึง
If Cells(i, 4).Value = 0 And Cells(i, 1).Value <> "" Then....หมายถึง
Cells(i + 1, 1).Value = Cells(i, 1).Value....หมายถึง
Cells(i, 1).EntireRow.Delete....หมายถึง
ElseIf Cells(i, 4).Value = 0 And Cells(i, 1).Value = "" Then....หมายถึง
Cells(i, 1).EntireRow.Delete....หมายถึง
End If....หมายถึง
Next i....หมายถึง
End Sub

[puriwutpokin]

Dim finalrow As Long....หมายถึง >> การประกาศตัวแปร finalrow ที่เป็นจำนวนเต็มที่มีค่า ระหว่าง (2,147,483,647) to 2,147,483,647 ในที่นี้ให้ finalrow เป็นจำนวนสุดท้ายของข้อมูลนี้
Dim i As Long....หมายถึง >> เช่นเดียวกับ อันแรก แต่เป็นตัวแปร เริ่มค่าแรก
finalrow = Cells(Rows.Count, 2).End(xlUp).Row....หมายถึง >> ตามที่กำหนดตัวแปรสุดท้ายตามข้อแรก ให้เท่ากับ Cells(Rows.Count, 2).End(xlUp).Row ที่ได้จากค่าสุดท้ายจากคอลัมน์ที่ 2
For i = finalrow To 4 Step -1....หมายถึง >> เป็นคำสั่งวนลูปในรูปของ For For i = finalrow เป็นเริ่มค่าแรกที่บรรทัดสุดท้ายของข้อมูล จนไปถึง 4 โดย ลบไปที่ละ 1 Step ขึ้นบน
If Cells(i, 4).Value = 0 And Cells(i, 1).Value <> "" Then....หมายถึง >> คือเงื่อนไข If หรือ ถ้า Cells(i, 4).Value = 0 And Cells(i, 1).Value <> "" Then หรือ ถ้าที่เซล คอลัมน์ที่ 4 ตำแหน่งแถวที่ i มีค่าเท่ากับศูนย์ และ เซล คอลัมน์ที่ 1 ตำแหน่งแถวที่ i ไม่เท่ากับค่าว่าง ให้ทำดังต่อไปนี้
Cells(i + 1, 1).Value = Cells(i, 1).Value....หมายถึง >> จากด้านบน ทำตังนี้ คือให้เอาค่าที่ ตำแหน่ง เซลที่ คอลัมน์ที่ 1 ตำแหน่งแถวที่ i บวกค่าลงไป 1 บรรทัด ให้เท่ากับค่า เซลที่ คอลัมน์ที่ 1 ตำแหน่งแถวที่ i
Cells(i, 1).EntireRow.Delete....หมายถึง >> คอลัมน์ที่ 1 ตำแหน่งแถวที่ i ให้ลบบรรทัด i ทั้งแถวแล้วยกบรรทัดล่างขึ้นไป
ElseIf Cells(i, 4).Value = 0 And Cells(i, 1).Value = "" Then....หมายถึง >> ถ้าคำสั่งก่อนหน้านี้ไม่จริงให้ใช้คำสั่งนี้แทน คือ ถ้าที่เซล คอลัมน์ที่ 4 ตำแหน่งแถวที่ i มีค่าเท่ากับศูนย์ และ เซล คอลัมน์ที่ 1 ตำแหน่งแถวที่ i เท่ากับค่าว่าง ให้ทำดังต่อไปนี้
Cells(i, 1).EntireRow.Delete....หมายถึง >> คอลัมน์ที่ 1 ตำแหน่งแถวที่ i ให้ลบบรรทัด i ทั้งแถวแล้วยกบรรทัดล่างขึ้นไป
End If....หมายถึง >> คือการจบ 1 Loop
Next i....หมายถึง >> ให้ Loop เลขต่อไปครับ ประมาณนี้ ลองศึกษาเพิ่มเติมได้จากเว็บนี้ ที่มีการถามกันนะครับ
End Sub

[pro602]

Dim finalrow As Long....หมายถึง >> การประกาศตัวแปร finalrow ที่เป็นจำนวนเต็มที่มีค่า ระหว่าง (2,147,483,647) to 2,147,483,647 ในที่นี้ให้ finalrow เป็นจำนวนสุดท้ายของข้อมูลนี้
Dim i As Long....หมายถึง >> เช่นเดียวกับ อันแรก แต่เป็นตัวแปร เริ่มค่าแรก
finalrow = Cells(Rows.Count, 2).End(xlUp).Row....หมายถึง >> ตามที่กำหนดตัวแปรสุดท้ายตามข้อแรก ให้เท่ากับ Cells(Rows.Count, 2).End(xlUp).Row ที่ได้จากค่าสุดท้ายจากคอลัมน์ที่ 2
For i = finalrow To 4 Step -1....หมายถึง >> เป็นคำสั่งวนลูปในรูปของ For For i = finalrow เป็นเริ่มค่าแรกที่บรรทัดสุดท้ายของข้อมูล จนไปถึง 4 โดย ลบไปที่ละ 1 Step ขึ้นบน
If Cells(i, 4).Value = 0 And Cells(i, 1).Value <> "" Then....หมายถึง >> คือเงื่อนไข If หรือ ถ้า Cells(i, 4).Value = 0 And Cells(i, 1).Value <> "" Then หรือ ถ้าที่เซล คอลัมน์ที่ 4 ตำแหน่งแถวที่ i มีค่าเท่ากับศูนย์ และ เซล คอลัมน์ที่ 1 ตำแหน่งแถวที่ i ไม่เท่ากับค่าว่าง ให้ทำดังต่อไปนี้
Cells(i + 1, 1).Value = Cells(i, 1).Value....หมายถึง >> จากด้านบน ทำตังนี้ คือให้เอาค่าที่ ตำแหน่ง เซลที่ คอลัมน์ที่ 1 ตำแหน่งแถวที่ i บวกค่าลงไป 1 บรรทัด ให้เท่ากับค่า เซลที่ คอลัมน์ที่ 1 ตำแหน่งแถวที่ i
Cells(i, 1).EntireRow.Delete....หมายถึง >> คอลัมน์ที่ 1 ตำแหน่งแถวที่ i ให้ลบบรรทัด i ทั้งแถวแล้วยกบรรทัดล่างขึ้นไป
ElseIf Cells(i, 4).Value = 0 And Cells(i, 1).Value = "" Then....หมายถึง >> ถ้าคำสั่งก่อนหน้านี้ไม่จริงให้ใช้คำสั่งนี้แทน คือ ถ้าที่เซล คอลัมน์ที่ 4 ตำแหน่งแถวที่ i มีค่าเท่ากับศูนย์ และ เซล คอลัมน์ที่ 1 ตำแหน่งแถวที่ i เท่ากับค่าว่าง ให้ทำดังต่อไปนี้
Cells(i, 1).EntireRow.Delete....หมายถึง >> คอลัมน์ที่ 1 ตำแหน่งแถวที่ i ให้ลบบรรทัด i ทั้งแถวแล้วยกบรรทัดล่างขึ้นไป
End If....หมายถึง >> คือการจบ 1 Loop
Next i....หมายถึง >> ให้ Loop เลขต่อไปครับ ประมาณนี้ ลองศึกษาเพิ่มเติมได้จากเว็บนี้ ที่มีการถามกันนะครับ
End Sub

ขอบคุณครับผม