Delete entire row if cells are blank

[LearnVBA83]

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.

Sub delete()




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


End Sub

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

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Scott T]

Does this do what you want

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

 

[LearnVBA83]

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!!!

 

[Scott T]

Try

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

 

[LearnVBA83]

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

 

[Scott T]

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

lr = Cells(Rows.Count, [COLOR=#FF0000]8[/COLOR]).End(xlUp).Row