Macro to delete rows

[wilko]

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

 

[kevcar40]

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 deleteand
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

 

[wilko]

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!

 

[Rick Rothstein]

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.