Macro to Delete Blank Rows

[howard]

I need a macro to delete blank rows from row 2, but excluding the last 4 rows where there is blank rows between the data

See example below where the rows containing blank cells after #2 in column A must not be deleted

Your assistance in this regard will be most apreciated

Howard

East.newvehicles.xls
	A	B	C	D	E	F	G
93	1	3.0DITSLXF/SCAB	19,245	0	19,245	Br2	1
94	2	RANGER3.0DITXLT4X2	18,245	0	18,245	Br1	1
95
96
97	TotalUnits	2
98
99	TotalValue		37,490	0	37,490
100
East.newvehicles

 

[njimack]

Assuming your data starts in row 2, and by 'blank' cells, you mean column A is empty...

Sub Del_Rows()
Dim End_Row As Long, n As Long

End_Row = Range("A1").End(xlDown).Row

For n = End_Row To 2 Step-1
With Cells(n,1)
If .Value = "" Then .EntireRow.Delete
End With
Next n

End Sub

 

[Mike Blackman]

Hi Howard,

Is there a column which will always have values in with exception to the blank rows?

 

[schielrn]

Maybe try:

sub deleteAllBut4()
dim lr as long
lr = cells(rows.count,1).end(xlup).offset(-4,0).row
range("A2:A" & lr).specialcells(xlcelltypeblanks).entirerow.delete
End Sub

This is untested, so please try on a copy of your workbook, but I believe it should work.

 

[howard]

Hi Guys thanks for the help.

Nkimack's macro, deletes the blank cell in row 2, but not an sunsequent rows containing blank cells.

Shielrn's macro deletes all the rows except the first row

See example below. it would be appreciated if you could assist

Regards

Howard

Delete Blanks.xls
	A	B	C	D
1	StockNo	Description	Basiccosts	DFA
2
3	1	FOCUS1.6AMBIENTE5DR	12,224	344
4	2	SPORT1.6ORIGINAL	13,450	340
5	3	IKON1.4AMBIENTE	10,385	344
6	4	FOCUS1.6AMBIENTE4DR	12,816	344
7	5	RANGER3.0DXLT4X2S/CA	19,732	344
8	6	FIESTA1.4BASE5DOOR	9,825	0
9	7	RANGER2.5TDXL4X2	13,640	344
10	8	52.0ACTIVE5DRMAN	12,350	343
11	9	21.3DYNAMIC5DR	110,038	0
12
13
14
15	10	BT-502.5TD4X2F/SCAB	12,870	357
16	11	3.0DITSLXF/SCAB	19,100	353
East.newvehicles

 

[njimack]

I did qualify my post by assuming your data started in row 2. Your sample starts in row 3, so try the following...

Sub Del_Rows()
    Dim End_Row As Long, n As Long

    End_Row = Range("A" & Rows.Count).End(xlUp).Row.Offset(-5, 0)

    For n = End_Row To 2 Step -1
        With Cells(n, 1)
            If .Value = "" Then .EntireRow.Delete
        End With
    Next n

End Sub

 

[schielrn]

I edited mine, as I didn't read the part about blank cells, but a loop is still not necessary:

sub deleteAllBut4()
dim lr as long
lr = cells(rows.count,1).end(xlup).offset(-4,0).row
range("A3:A" & lr).specialcells(xlcelltypeblanks).entirerow.delete
End Sub

Hope that helps.

 

[howard]

Hi Njimack

It now comes up with a Compile error: Invalid Qualifier

End_Row = Range("A" & Rows.Count).End(xlUp).Row.Offset(-5, 0)

It would be appreciated if you would amend your code

Regards

Howard

 

[schielrn]

I think you may need to change this:

End_Row = Range("A" & Rows.Count).End(xlUp).Row.Offset(-5, 0)

to:

End_Row = Range("A" & Rows.Count).End(xlUp).Offset(-5, 0).Row

But personally I wouldn't use a loop, but to each one's own.

Hope that helps.

 

[howard]

Hi Schielrn

Thanks for the help. Code now works perfectly

Regards

Howard