Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete & Merge Columns,Delete Rows with filter, etc | Excel Programming | |||
Delete Rows if any cell in Column H is blank but do not Delete Fir | Excel Programming | |||
Copy pasting Rows, but need to Delete any Shapes/Pictures that are within copied rows | Excel Programming | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |