delete certain rows only

T

tom mcd

below is the spreadsheet I have with a lot of data. I need to keep only the
data which has in cell column (status) c: the value 1. If the value is 0 then
the rows above the 0 are not required. As there are over 15,000 rows and no
set amount of rows for each block (column A0 does anyone have a function or
module solution for this query. Thanks again all.

block house status
100x Perth Crescent 100 Perth Crescent A
102 Perth Crescent S
0


1015x Dumbarton Road 1019 Dumbarton Road A
1015 Dumbarton Road S
1017 Dumbarton Road S
1

101x Craigs Avenue 101 Craigs Avenue A
107 Craigs Avenue A
103 Craigs Avenue S
105 Craigs Avenue S
1
 
S

Simon Lloyd

Will there always be a blank row above the block of data as you hav
shown
below is the spreadsheet I have with a lot of data. I need to keep onl
th
data which has in cell column (status) c: the value 1. If the value i
0 the
the rows above the 0 are not required. As there are over 15,000 row
and n
set amount of rows for each block (column A0 does anyone have
function o
module solution for this query. Thanks again all

block house statu
100x Perth Crescent 100 Perth Crescent
102 Perth Crescent



1015x Dumbarton Road 1019 Dumbarton Road
1015 Dumbarton Road
1017 Dumbarton Road


101x Craigs Avenue 101 Craigs Avenue
107 Craigs Avenue
103 Craigs Avenue
105 Craigs Avenue
Click to expand...

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com
 
D

Don Guillett

Sub deleteblocks()
'insert a row at row2 FIRST
mc = 1 'Column A
For i = Cells(Rows.Count, mc).End(xlUp) _
.Row To 2 Step -1
If Len(Application.Trim(Cells(i, mc))) > 0 _
And Cells(i, mc) = 0 Then
Range(Cells(i, mc), Cells(i, mc).End(xlUp)) _
.EntireRow.Delete
End If
Next i
End Sub
 
T

tom_mcd

Brilliant Don, thanks a lot.

Don Guillett said:
Sub deleteblocks()
'insert a row at row2 FIRST
mc = 1 'Column A
For i = Cells(Rows.Count, mc).End(xlUp) _
.Row To 2 Step -1
If Len(Application.Trim(Cells(i, mc))) > 0 _
And Cells(i, mc) = 0 Then
Range(Cells(i, mc), Cells(i, mc).End(xlUp)) _
.EntireRow.Delete
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
Click to expand...
 
Top