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

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

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

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