Delete entire row if cells are blank

L

LearnVBA83

Board Regular
Joined
Dec 1, 2016
Messages
113
Office Version
  1. 365
Platform
  1. Windows
Hi team,

Does anyone know how to fix the below code. basically I want to delete the entire row if columns A - G are blank.

Code: 
Sub delete()




Columns("A:G").SpecialCells(xlCellTypeBlanks).EntireRow.delete


End Sub


[TABLE="width: 946"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]CHK NBR CH[/TD]
[TD]K ACCT NBR[/TD]
[TD]CHK ABA #[/TD]
[TD]CHECK AMT[/TD]
[TD]INVOICE AMT[/TD]
[TD]INVOICE[/TD]
[TD]# CUST #[/TD]
[TD]BATCH[/TD]
[TD]SEQ#[/TD]
[TD]SYS SEQ #[/TD]
[TD]Lock Box[/TD]
[TD]Deposit Date[/TD]
[TD]Tie Out[/TD]
[/TR]
[TR]
[TD="align: right"]1840[/TD]
[TD]446017480976[/TD]
[TD]52001633[/TD]
[TD="align: right"]3362.65[/TD]
[TD="align: right"]3362.65[/TD]
[TD]H4485613[/TD]
[TD]890831681001[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]110001[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]110001[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2928[/TD]
[TD]24356752[/TD]
[TD]221271935[/TD]
[TD="align: right"]263.05[/TD]
[TD="align: right"]263.05[/TD]
[TD]H4457564[/TD]
[TD]800423331001[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]110002[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]110002[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1294[/TD]
[TD]4310682508[/TD]
[TD]31201360[/TD]
[TD="align: right"]1387.4[/TD]
[TD="align: right"]1387.4[/TD]
[TD]H4496286[/TD]
[TD]914878821001[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]110003[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]110003[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]11638[/TD]
[TD]3220020567[/TD]
[TD]122000496[/TD]
[TD="align: right"]249.51[/TD]
[TD="align: right"]249.51[/TD]
[TD]H4467765[/TD]
[TD]823132671001[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]110004[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]110004[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1925[/TD]
[TD]50104954842[/TD]
[TD]11075150[/TD]
[TD="align: right"]16799.14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2000001[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]16799.14[/TD]
[/TR]
[TR]
[TD="align: right"]2294[/TD]
[TD]3301085303[/TD]
[TD]121140399[/TD]
[TD="align: right"]340.69[/TD]
[TD="align: right"]17139.83[/TD]
[TD]H4479700[/TD]
[TD]864775941002[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2000001[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]-16799.14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2000001[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]8176[/TD]
[TD]4349901293[/TD]
[TD]221272031[/TD]
[TD="align: right"]853.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2000002[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]853.4[/TD]
[/TR]
[TR]
[TD="align: right"]7098[/TD]
[TD]690582023[/TD]
[TD]21000021[/TD]
[TD="align: right"]134.8[/TD]
[TD="align: right"]988.2[/TD]
[TD]H4450757[/TD]
[TD]113671801003[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2000002[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]-853.4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2000002[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]34605[/TD]
[TD]6378839291[/TD]
[TD]121042882[/TD]
[TD="align: right"]321.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2000003[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]321.1[/TD]
[/TR]
[TR]
[TD="align: right"]15998[/TD]
[TD]325059351553[/TD]
[TD]121000358[/TD]
[TD="align: right"]189.8[/TD]
[TD="align: right"]510.9[/TD]
[TD]H4450099[/TD]
[TD]106785121001[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2000003[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]-321.1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2000003[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]292996[/TD]
[TD]4172660987[/TD]
[TD]121000248[/TD]
[TD="align: right"]730.86[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2000004[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]730.86[/TD]
[/TR]
[TR]
[TD="align: right"]102792[/TD]
[TD]4128045259[/TD]
[TD]121000248[/TD]
[TD="align: right"]13947[/TD]
[TD="align: right"]14677.86[/TD]
[TD]H4519013[/TD]
[TD]952946591001[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]2000004[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]-730.86[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2000004[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3238[/TD]
[TD]58016694[/TD]
[TD]221172212[/TD]
[TD="align: right"]793.43[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]2000005[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]793.43[/TD]
[/TR]
[TR]
[TD="align: right"]4701[/TD]
[TD]1374346[/TD]
[TD]21000021[/TD]
[TD="align: right"]2155.42[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]2000005[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]2155.42[/TD]
[/TR]
[TR]
[TD="align: right"]1648[/TD]
[TD]4301358704[/TD]
[TD]11103093[/TD]
[TD="align: right"]2155.42[/TD]
[TD="align: right"]5104.27[/TD]
[TD]H4524547[/TD]
[TD]962326641001[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2000005[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]-2948.85[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2000005[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]03/12/2017[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Does this do what you want

Code: 
Sub delblank()
Dim lr As Long
lr = Cells(Rows.Count, 8).End(xlUp).Row
For x = lr To 2 Step -1
    If Application.WorksheetFunction.CountBlank(Range("A" & x & ":G" & x)) Then
        Rows(x).EntireRow.Delete
    End If
Next x

End Sub
 
Upvote 0
Hi Scott T,

Thanks for the help. That code almost works. I need it delete the entire row only if columns A, B, C, D, E, F, G are all blank together. So if there is any information in any of those columns then I want the macro to skip it, but if all 7 columns are blank in a row then i want to delete the entire row. Thanks for the help!!!
 
Upvote 0
Try

Code: 
Sub delblank()
Dim lr As Long
lr = Cells(Rows.Count, 8).End(xlUp).Row
For x = lr To 2 Step -1
    If Application.WorksheetFunction.CountBlank(Range("A" & x & ":G" & x)) = 7 Then
        Rows(x).EntireRow.Delete
    End If
Next x
End Sub
 
Upvote 0
Hi Scott T,

That seems to work fine, although I'm having trouble getting it to run on my whole data set. Do you think 500,000+ lines is too much?

Thanks - B
 
Upvote 0
With that many lines it will take some time to go though all the line but it should work.

Right new the code uses column H the 8th column) to find the last row. If need you should change this to a column that will always have data so you get the correct last row
Code: 
lr = Cells(Rows.Count, [COLOR=#FF0000]8[/COLOR]).End(xlUp).Row
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Copy data from report header to each transaction.
Replies
3
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top