delete certain rows only
 

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

delete certain rows only
 

Will there always be a blank row above the block of data as you have
shown?

tom mcd;505043 Wrote:
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


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=138900

delete certain rows only
 

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

"tom mcd" <tom wrote in message
...
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

delete certain rows only
 

Yes Simon there will always be a blank row above each block of data

"Simon Lloyd" wrote:


Will there always be a blank row above the block of data as you have
shown?

tom mcd;505043 Wrote:
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


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=138900

delete certain rows only
 

Did you see my solution?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"tom mcd" wrote in message
...
Yes Simon there will always be a blank row above each block of data

"Simon Lloyd" wrote:


Will there always be a blank row above the block of data as you have
shown?

tom mcd;505043 Wrote:
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


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=138900

delete certain rows only
 

Brilliant Don, thanks a lot.

"Don Guillett" wrote:

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

"tom mcd" <tom wrote in message
...
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