• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete entire row if certain range of data is blank

P

paradise

Member
Hi,
I want to delete entire rows if all the cells suppose from H6:K6 are blanks.Pls note that there might be data on A6,B6,C6,D6,E6,F6,G6

How this can be done by vba
 
Hi !​
​
If [COUNTBLANK(H6:K6)] = 4 Then …​
​
Do you like it ? So thanks to click on bottom right Like !
​
 
Thanks for the rules.Kindly know the following information.
Worksheet name=Data
Headers are from A5:K5
Entire Row/s to be deleted from A6:K6 and below if column data H:K are blank.

I hope this would be suffice info.
 
How about
Code: 
Sub paradise()
   Dim UsdRws As Long
   With Sheets("pcode")
      If .AutoFilterMode Then .AutoFilterMode = False
      UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
      .Range("A5:K" & UsdRws).AutoFilter 8, ""
      .Range("A5:K" & UsdRws).AutoFilter 9, ""
      .Range("A5:K" & UsdRws).AutoFilter 10, ""
      .Range("A5:K" & UsdRws).AutoFilter 11, ""
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub
 
Ah!! it did not work.

Kindly find enclosed in attachment sample demo data with required result which is highlighted in yellow in a separate worksheet name.

I hope this will ease further.
 

Attachments

  • test-1.xlsm
    15.9 KB · Views: 13
Thanx for your kind reply.

But I am expecting macro/vba code instead of Power Pivot as it would speed up my rest of my remaining process.
 
Last edited:
What about this?
Code: 
Sub belle()
Dim i As Long
For i = Cells(Rows.Count, 3).End(xlUp).Row To 6 Step -1
    If Cells(i, 8) = "" And Cells(i, 9) = "" And Cells(i, 10) = "" And Cells(i, 11) = "" Then Range("C" & i).EntireRow.Delete
Next
End Sub
 
According to the attachment :​
Code: 
Sub Demo1()
         Dim C&
         Application.ScreenUpdating = False
    With Sheet1.[C5].CurrentRegion.Columns("F:I")
         For C = 1 To .Count:  .AutoFilter C, "", xlOr, 0:  Next
        .Offset(1).EntireRow.Delete
        .Parent.AutoFilterMode = False
    End With
         Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
​
 
Belleke said:
What about this?
Code: 
Sub belle()
Dim i As Long
For i = Cells(Rows.Count, 3).End(xlUp).Row To 6 Step -1
    If Cells(i, 8) = "" And Cells(i, 9) = "" And Cells(i, 10) = "" And Cells(i, 11) = "" Then Range("C" & i).EntireRow.Delete
Next
End Sub
Click to expand...

This works like a charm and worked must faster than expected.Thanx a lot.

Could you plz add one more condition in it.If those cells are either "" i.e blank or "0" (zero)

I hope you can do this too.
 
Just replace the first code with this one.
= 0 works for both, empty cells and cells containing 0
Code: 
Sub belle()
Dim i As Long
For i = Cells(Rows.Count, 3).End(xlUp).Row To 6 Step -1
    If Cells(i, 8) = 0 And Cells(i, 9) = 0 And Cells(i, 10) = 0 And Cells(i, 11) = 0 Then Range("C" & i).EntireRow.Delete
Next
End Sub
 
Kindly note one more things is that it slows down drastically when I apply to more than 200 rows as in 1st column I do have check boxes also.I hope you will bring out some faster type code.In the meantime I using it.
 
As Belleke's code is the slowest way and as you already had a faster one …​
​
A cristal clear initial post with an according attachment avoid this kind of issue, as a reminder for next time.​
​
 
No,Belleke's code in sample demo as mentioned above work quickest and does the job as desired.But,when I tested to my real workbook with tons of data and checkboxes it then 'Belleke's code'gets slowed down.
Currently,I am using 'Belleke's code' only hoping that it would be further be trimmed.
 
ouate%20else.gif
 
You must log in or register to reply here.
Back
Top