Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete rows
Hi I have a very large spreadsheet that has been produced from a query in
access. The problem is the query repeated a lot of information that I now need to get rid of. I have a nnumber of projects each with a number of other data items next to them, some of these have been repeated. I have taken out the repeated items by using a countif formula but now I am stuck with 0 in their place instead. This is an example: ID 1999 AA Measure complete June 2009 1999 AA 0 Find reaso June 2009 1999 AA 0 0 June 2009 1999 AA 0 0 June 2009 1999 AA 0 0 June 2009 2143 LA CB FMH conducted April 2009 2143 LA CB 0 proforma Nov 2009 2143 LA CB 0 advice Sep 2009 So I need to delete the zeros within each 0 but not mix up the information for each project. I was thinking like something along the lines of delete and shift up for just the set of IDs but I'm not sure how to go about doing this! Any advice would be very much appreciated! Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete rows
On 23 July, 10:18, wilko wrote:
Hi I have a very large spreadsheet that has been produced from a query in access. The problem is the query repeated a lot of information that I now need to get rid of. I have a nnumber of projects each with a number of other data items next to them, some of these have been repeated. I have taken out the repeated items by using a countif formula but now I am stuck with 0 in their place instead. This is an example: ID 1999 * * * * * *AA * * * * * * *Measure complete * * * *June 2009 1999 * * * * * *AA * * * * * * *0 * * * Find reaso * * *June 2009 1999 * * * * * *AA * * * * * * *0 * * * 0 * * * June 2009 1999 * * * * * *AA * * * * * * *0 * * * 0 * * * June 2009 1999 * * * * * *AA * * * * * * *0 * * * 0 * * * June 2009 2143 * * * * * *LA * * *CB * * *FMH * * conducted * * * April 2009 2143 * * * * * *LA * * *CB * * *0 * * * proforma * * * *Nov 2009 2143 * * * * * *LA * * *CB * * *0 * * * advice *Sep 2009 So I need to delete the zeros within each 0 but not mix up the information for each project. I was thinking like something along the lines of delete and shift up for just the set of IDs but I'm not sure how to go about doing this! Any advice would be very much appreciated! Thank you hi wilko i use the routine below x = Cells(Rows.Count, "a").End(xlUp).Row For i = x To 2 Step -1 If Cells(i, "a") = "Equipment" Or Cells(i, "a") = 0 Then Cells (i, "a").EntireRow.Delete Next where is says 'Equipment change it to 0 change 'a' to represent the column you are looking at HTH Kevin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete rows
Hi Kevcar,
Thank you for that. I think this will delete entire rows but I just want to delete certian cells. but keep all the rows? But I don't want to shift everything up as this will mix up my data into other IDs? Any ideas? Thanks! "kevcar40" wrote: On 23 July, 10:18, wilko wrote: Hi I have a very large spreadsheet that has been produced from a query in access. The problem is the query repeated a lot of information that I now need to get rid of. I have a nnumber of projects each with a number of other data items next to them, some of these have been repeated. I have taken out the repeated items by using a countif formula but now I am stuck with 0 in their place instead. This is an example: ID 1999 AA Measure complete June 2009 1999 AA 0 Find reaso June 2009 1999 AA 0 0 June 2009 1999 AA 0 0 June 2009 1999 AA 0 0 June 2009 2143 LA CB FMH conducted April 2009 2143 LA CB 0 proforma Nov 2009 2143 LA CB 0 advice Sep 2009 So I need to delete the zeros within each 0 but not mix up the information for each project. I was thinking like something along the lines of delete and shift up for just the set of IDs but I'm not sure how to go about doing this! Any advice would be very much appreciated! Thank you hi wilko i use the routine below x = Cells(Rows.Count, "a").End(xlUp).Row For i = x To 2 Step -1 If Cells(i, "a") = "Equipment" Or Cells(i, "a") = 0 Then Cells (i, "a").EntireRow.Delete Next where is says 'Equipment change it to 0 change 'a' to represent the column you are looking at HTH Kevin |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete rows
So those 0's are the result of a formula then, right? If so, just modify
your formula to return a blank. For example, using <formula to stand in for your formula (minus the leading = sign)... =IF(<formula=0,"",<formula) Just put your formula in the two locations indicated and an empty cell will be displayed whenever your formula evaluates to 0. -- Rick (MVP - Excel) "wilko" wrote in message ... Hi I have a very large spreadsheet that has been produced from a query in access. The problem is the query repeated a lot of information that I now need to get rid of. I have a nnumber of projects each with a number of other data items next to them, some of these have been repeated. I have taken out the repeated items by using a countif formula but now I am stuck with 0 in their place instead. This is an example: ID 1999 AA Measure complete June 2009 1999 AA 0 Find reaso June 2009 1999 AA 0 0 June 2009 1999 AA 0 0 June 2009 1999 AA 0 0 June 2009 2143 LA CB FMH conducted April 2009 2143 LA CB 0 proforma Nov 2009 2143 LA CB 0 advice Sep 2009 So I need to delete the zeros within each 0 but not mix up the information for each project. I was thinking like something along the lines of delete and shift up for just the set of IDs but I'm not sure how to go about doing this! Any advice would be very much appreciated! Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows using a macro | Excel Discussion (Misc queries) | |||
macro to delete rows | Excel Discussion (Misc queries) | |||
Macro to delete rows | Excel Discussion (Misc queries) | |||
Need a macro to delete rows | Excel Discussion (Misc queries) | |||
delete rows-macro | Excel Discussion (Misc queries) |