I have a file that comes in weekly that I take numerous steps to clean up the
data for my reports. I am hoping to automate some of these. Could one of
you experts help me to create a macro to do the following:

Every third column (beginning with C thru X) contains SUMIF formulas to
calculate totals - ie: =IF(SUMIF(Breakdown!D:D,T6,Breakdown!C:C)<1,"
",SUMIF(Breakdown!D:D,T6,Breakdown!C:C)).

If the formula results in a blank, then I need to delete that cell and the
two cells to the right of it, and move the rest of the cells up.

For example, if C3 and C6 are blank then I need Cells A3, B3, C3, A6, B6,
and C6 to be deleted and all existing cells in those columns to move up.
This needs to be done for all the blank cells in the totals columns stated
above.

I have some experience with macros and VBA, but I need some help getting
started on this one....

Thanks,
Diane

Diane
Don't you mean "two cells to the LEFT"? Your explanation says to the
right but your example shows to the left.
The macro would look like this:
Sub DeleteCells()
Dim c As Long
Dim d As Long
Dim TheRng As Range
For c = 3 To 24 Step 3
Set TheRng = Range(Cells(2, c), Cells(Rows.Count, c).End(xlUp))
For d = TheRng.Count To 1 Step -1
If TheRng(d).Value = "" Then _
TheRng(d).Offset(, -2).Resize(, 3).Delete
Shift:=xlUp
Next d
Next c
End Sub
Please post back if you need more. HTH Otto
"diaare" wrote in message
...
I have a file that comes in weekly that I take numerous steps to clean up
the
data for my reports. I am hoping to automate some of these. Could one of
you experts help me to create a macro to do the following:

Every third column (beginning with C thru X) contains SUMIF formulas to
calculate totals - ie: =IF(SUMIF(Breakdown!D:D,T6,Breakdown!C:C)<1,"
",SUMIF(Breakdown!D:D,T6,Breakdown!C:C)).

If the formula results in a blank, then I need to delete that cell and the
two cells to the right of it, and move the rest of the cells up.

For example, if C3 and C6 are blank then I need Cells A3, B3, C3, A6, B6,
and C6 to be deleted and all existing cells in those columns to move up.
This needs to be done for all the blank cells in the totals columns stated
above.

I have some experience with macros and VBA, but I need some help getting
started on this one....

Thanks,
Diane

Thnaks for the response. You were correct in assuming I meant Left of the
cell. My fingers don't always type what I mean.

I pasted your macro in and recieved an error on Shift:=xlUp I changed it to
Shift = xlUp and it runs now. And it does exacly what I asked for,
unfortunately I didn't think through my question entirely...

Here is the problem, I need to add another condition to this rule if
possible. (I didn't think about it until I saw it run).

If there is a value in the cell 2 cells to the left of the blank, I do not
want it to delete the cells in that row. So, really what I want is to find
where cells in the totals column that are blank, and if the cell two to the
left are blank as well, then delete those two cells and the one between them.

For example:

A B C D E F
1 x 5 x x 3
2 x x 4
3 4 x x
4 x 6 5 x

I would only want to delete A2, B2, C2 and D3, E3, F3

This is proving to be more complicated than I thought...is it still doable?

Thnaks again for your help.

"Otto Moehrbach" wrote:

Diane
Don't you mean "two cells to the LEFT"? Your explanation says to the
right but your example shows to the left.
The macro would look like this:
Sub DeleteCells()
Dim c As Long
Dim d As Long
Dim TheRng As Range
For c = 3 To 24 Step 3
Set TheRng = Range(Cells(2, c), Cells(Rows.Count, c).End(xlUp))
For d = TheRng.Count To 1 Step -1
If TheRng(d).Value = "" Then _
TheRng(d).Offset(, -2).Resize(, 3).Delete
Shift:=xlUp
Next d
Next c
End Sub
Please post back if you need more. HTH Otto
"diaare" wrote in message
...
I have a file that comes in weekly that I take numerous steps to clean up
the
data for my reports. I am hoping to automate some of these. Could one of
you experts help me to create a macro to do the following:

Every third column (beginning with C thru X) contains SUMIF formulas to
calculate totals - ie: =IF(SUMIF(Breakdown!D:D,T6,Breakdown!C:C)<1,"
",SUMIF(Breakdown!D:D,T6,Breakdown!C:C)).

If the formula results in a blank, then I need to delete that cell and the
two cells to the right of it, and move the rest of the cells up.

For example, if C3 and C6 are blank then I need Cells A3, B3, C3, A6, B6,
and C6 to be deleted and all existing cells in those columns to move up.
This needs to be done for all the blank cells in the totals columns stated
above.

I have some experience with macros and VBA, but I need some help getting
started on this one....

Thanks,
Diane

Diane
Try this. I also added some code to freeze the screen so you won't see
things jumping around as cells are deleted. The jumping around (called
"painting the screen") takes a lot of time so this way the code will run
faster. HTH Otto
Sub DeleteCells()
Dim c As Long
Dim d As Long
Dim TheRng As Range
Application.ScreenUpdating = False
For c = 3 To 24 Step 3
Set TheRng = Range(Cells(2, c), Cells(Rows.Count, c).End(xlUp))
For d = TheRng.Count To 1 Step -1
If TheRng(d).Value = "" And TheRng(d).Offset(, -2).Value =
"" Then _
TheRng(d).Offset(, -2).Resize(, 3).Delete
Shift:=xlUp
Next d
Next c
Application.ScreenUpdating = True
End Sub
"diaare" wrote in message
...
Thnaks for the response. You were correct in assuming I meant Left of the
cell. My fingers don't always type what I mean.

I pasted your macro in and recieved an error on Shift:=xlUp I changed it
to
Shift = xlUp and it runs now. And it does exacly what I asked for,
unfortunately I didn't think through my question entirely...

Here is the problem, I need to add another condition to this rule if
possible. (I didn't think about it until I saw it run).

If there is a value in the cell 2 cells to the left of the blank, I do not
want it to delete the cells in that row. So, really what I want is to
find
where cells in the totals column that are blank, and if the cell two to
the
left are blank as well, then delete those two cells and the one between
them.

For example:

A B C D E F
1 x 5 x x 3
2 x x 4
3 4 x x
4 x 6 5 x

I would only want to delete A2, B2, C2 and D3, E3, F3

This is proving to be more complicated than I thought...is it still
doable?

Thnaks again for your help.

"Otto Moehrbach" wrote:

Diane
Don't you mean "two cells to the LEFT"? Your explanation says to the
right but your example shows to the left.
The macro would look like this:
Sub DeleteCells()
Dim c As Long
Dim d As Long
Dim TheRng As Range
For c = 3 To 24 Step 3
Set TheRng = Range(Cells(2, c), Cells(Rows.Count,
c).End(xlUp))
For d = TheRng.Count To 1 Step -1
If TheRng(d).Value = "" Then _
TheRng(d).Offset(, -2).Resize(, 3).Delete
Shift:=xlUp
Next d
Next c
End Sub
Please post back if you need more. HTH Otto
"diaare" wrote in message
...
I have a file that comes in weekly that I take numerous steps to clean
up
the
data for my reports. I am hoping to automate some of these. Could one
of
you experts help me to create a macro to do the following:

Every third column (beginning with C thru X) contains SUMIF formulas to
calculate totals - ie: =IF(SUMIF(Breakdown!D:D,T6,Breakdown!C:C)<1,"
",SUMIF(Breakdown!D:D,T6,Breakdown!C:C)).

If the formula results in a blank, then I need to delete that cell and
the
two cells to the right of it, and move the rest of the cells up.

For example, if C3 and C6 are blank then I need Cells A3, B3, C3, A6,
B6,
and C6 to be deleted and all existing cells in those columns to move
up.
This needs to be done for all the blank cells in the totals columns
stated
above.

I have some experience with macros and VBA, but I need some help
getting
started on this one....

Thanks,
Diane

Thanks for your response. I was happy see that the change you made was what
I was going to do, I guess know more VBA than I thought.

Unfortunately, when I try to run it brings my system to a halt, until I
finally have to close Excel through Task Manager. Any ideas on what I could
do to speed it up? If not maybe I'll just have to continue on with the
manual process...

Thanks,
Diane

"Otto Moehrbach" wrote:

Diane
Try this. I also added some code to freeze the screen so you won't see
things jumping around as cells are deleted. The jumping around (called
"painting the screen") takes a lot of time so this way the code will run
faster. HTH Otto
Sub DeleteCells()
Dim c As Long
Dim d As Long
Dim TheRng As Range
Application.ScreenUpdating = False
For c = 3 To 24 Step 3
Set TheRng = Range(Cells(2, c), Cells(Rows.Count, c).End(xlUp))
For d = TheRng.Count To 1 Step -1
If TheRng(d).Value = "" And TheRng(d).Offset(, -2).Value =
"" Then _
TheRng(d).Offset(, -2).Resize(, 3).Delete
Shift:=xlUp
Next d
Next c
Application.ScreenUpdating = True
End Sub
"diaare" wrote in message
...
Thnaks for the response. You were correct in assuming I meant Left of the
cell. My fingers don't always type what I mean.

I pasted your macro in and recieved an error on Shift:=xlUp I changed it
to
Shift = xlUp and it runs now. And it does exacly what I asked for,
unfortunately I didn't think through my question entirely...

Here is the problem, I need to add another condition to this rule if
possible. (I didn't think about it until I saw it run).

If there is a value in the cell 2 cells to the left of the blank, I do not
want it to delete the cells in that row. So, really what I want is to
find
where cells in the totals column that are blank, and if the cell two to
the
left are blank as well, then delete those two cells and the one between
them.

For example:

A B C D E F
1 x 5 x x 3
2 x x 4
3 4 x x
4 x 6 5 x

I would only want to delete A2, B2, C2 and D3, E3, F3

This is proving to be more complicated than I thought...is it still
doable?

Thnaks again for your help.

"Otto Moehrbach" wrote:

Diane
Don't you mean "two cells to the LEFT"? Your explanation says to the
right but your example shows to the left.
The macro would look like this:
Sub DeleteCells()
Dim c As Long
Dim d As Long
Dim TheRng As Range
For c = 3 To 24 Step 3
Set TheRng = Range(Cells(2, c), Cells(Rows.Count,
c).End(xlUp))
For d = TheRng.Count To 1 Step -1
If TheRng(d).Value = "" Then _
TheRng(d).Offset(, -2).Resize(, 3).Delete
Shift:=xlUp
Next d
Next c
End Sub
Please post back if you need more. HTH Otto
"diaare" wrote in message
...
I have a file that comes in weekly that I take numerous steps to clean
up
the
data for my reports. I am hoping to automate some of these. Could one
of
you experts help me to create a macro to do the following:

Every third column (beginning with C thru X) contains SUMIF formulas to
calculate totals - ie: =IF(SUMIF(Breakdown!D:D,T6,Breakdown!C:C)<1,"
",SUMIF(Breakdown!D:D,T6,Breakdown!C:C)).

If the formula results in a blank, then I need to delete that cell and
the
two cells to the right of it, and move the rest of the cells up.

For example, if C3 and C6 are blank then I need Cells A3, B3, C3, A6,
B6,
and C6 to be deleted and all existing cells in those columns to move
up.
This needs to be done for all the blank cells in the totals columns
stated
above.

I have some experience with macros and VBA, but I need some help
getting
started on this one....

Thanks,
Diane

Diane
I have Excel XP, also known as 2002. I made up some dummy data in a
file and ran the code I gave you and it works just fine. I can't point to
any one thing that might be causing your problems, but here are some things
you might look at.
Do you have a module and a macro by the same name? Excel tends to hang up
when this happens.
Do you have multiple macros by the same name? This usually causes an error
message and you didn't say you had an error message.
Are you running this file within a network containing main servers at your
work? Perhaps your IT people have inserted a block into the system to stop
all automation that hasn't been cleared by them first.
Short of looking at your file, there is not much I can do. The code I gave
you is in no way complex or strange. It should run without any problem at
all.
If you wish, send me your file via email (don't post your file in the
newsgroup) and I'll look at it. Fake the data as you wish to protect it, I
need only the layout of the data and the file. Make sure that your problems
can be repeated on the file you send. That's important. Also, be sure to
send the file as it is when you experience the problem. In other words,
don't delete any code or modules or sheets. Don't delete or change any
protection that your file has. If there is protection and that protection
includes passwords, include the passwords or change the passwords or delete
the passwords. But make sure that your problems still exist on the file you
send.
Also tell me the version of Excel that you are running.
My email address is . Remove the "nop" from this
address. HTH Otto
"diaare" wrote in message
...
Thanks for your response. I was happy see that the change you made was
what
I was going to do, I guess know more VBA than I thought.

Unfortunately, when I try to run it brings my system to a halt, until I
finally have to close Excel through Task Manager. Any ideas on what I
could
do to speed it up? If not maybe I'll just have to continue on with the
manual process...

Thanks,
Diane

"Otto Moehrbach" wrote:

Diane
Try this. I also added some code to freeze the screen so you won't
see
things jumping around as cells are deleted. The jumping around (called
"painting the screen") takes a lot of time so this way the code will run
faster. HTH Otto
Sub DeleteCells()
Dim c As Long
Dim d As Long
Dim TheRng As Range
Application.ScreenUpdating = False
For c = 3 To 24 Step 3
Set TheRng = Range(Cells(2, c), Cells(Rows.Count,
c).End(xlUp))
For d = TheRng.Count To 1 Step -1
If TheRng(d).Value = "" And
TheRng(d).Offset(, -2).Value =
"" Then _
TheRng(d).Offset(, -2).Resize(, 3).Delete
Shift:=xlUp
Next d
Next c
Application.ScreenUpdating = True
End Sub
"diaare" wrote in message
...
Thnaks for the response. You were correct in assuming I meant Left of
the
cell. My fingers don't always type what I mean.

I pasted your macro in and recieved an error on Shift:=xlUp I changed
it
to
Shift = xlUp and it runs now. And it does exacly what I asked for,
unfortunately I didn't think through my question entirely...

Here is the problem, I need to add another condition to this rule if
possible. (I didn't think about it until I saw it run).

If there is a value in the cell 2 cells to the left of the blank, I do
not
want it to delete the cells in that row. So, really what I want is to
find
where cells in the totals column that are blank, and if the cell two to
the
left are blank as well, then delete those two cells and the one between
them.

For example:

A B C D E F
1 x 5 x x 3
2 x x 4
3 4 x x
4 x 6 5 x

I would only want to delete A2, B2, C2 and D3, E3, F3

This is proving to be more complicated than I thought...is it still
doable?

Thnaks again for your help.

"Otto Moehrbach" wrote:

Diane
Don't you mean "two cells to the LEFT"? Your explanation says to
the
right but your example shows to the left.
The macro would look like this:
Sub DeleteCells()
Dim c As Long
Dim d As Long
Dim TheRng As Range
For c = 3 To 24 Step 3
Set TheRng = Range(Cells(2, c), Cells(Rows.Count,
c).End(xlUp))
For d = TheRng.Count To 1 Step -1
If TheRng(d).Value = "" Then _
TheRng(d).Offset(, -2).Resize(, 3).Delete
Shift:=xlUp
Next d
Next c
End Sub
Please post back if you need more. HTH Otto
"diaare" wrote in message
...
I have a file that comes in weekly that I take numerous steps to
clean
up
the
data for my reports. I am hoping to automate some of these. Could
one
of
you experts help me to create a macro to do the following:

Every third column (beginning with C thru X) contains SUMIF formulas
to
calculate totals - ie: =IF(SUMIF(Breakdown!D:D,T6,Breakdown!C:C)<1,"
",SUMIF(Breakdown!D:D,T6,Breakdown!C:C)).

If the formula results in a blank, then I need to delete that cell
and
the
two cells to the right of it, and move the rest of the cells up.

For example, if C3 and C6 are blank then I need Cells A3, B3, C3,
A6,
B6,
and C6 to be deleted and all existing cells in those columns to move
up.
This needs to be done for all the blank cells in the totals columns
stated
above.

I have some experience with macros and VBA, but I need some help
getting
started on this one....

Thanks,
Diane

Okay - I have multiple macros in this file, so to troubleshoot:

I copied my data into a new workbook and only put in your macro. When I try
to run it I get an error popup that is a big red x with "400" as the message.

We are on a network, but I have created many other macros that run without a
problem, so I don't think it is an IT issue.

In the meantime, I am going to try and get with someone here and see if they
can help diagnose the problem. I appreciate your offer to look at the file,
but I know that sending any attachements out is frowned upon here, so I will
use it as a last resort.

Thanks so much for all of your help,
Diane

"Otto Moehrbach" wrote:

Diane
I have Excel XP, also known as 2002. I made up some dummy data in a
file and ran the code I gave you and it works just fine. I can't point to
any one thing that might be causing your problems, but here are some things
you might look at.
Do you have a module and a macro by the same name? Excel tends to hang up
when this happens.
Do you have multiple macros by the same name? This usually causes an error
message and you didn't say you had an error message.
Are you running this file within a network containing main servers at your
work? Perhaps your IT people have inserted a block into the system to stop
all automation that hasn't been cleared by them first.
Short of looking at your file, there is not much I can do. The code I gave
you is in no way complex or strange. It should run without any problem at
all.
If you wish, send me your file via email (don't post your file in the
newsgroup) and I'll look at it. Fake the data as you wish to protect it, I
need only the layout of the data and the file. Make sure that your problems
can be repeated on the file you send. That's important. Also, be sure to
send the file as it is when you experience the problem. In other words,
don't delete any code or modules or sheets. Don't delete or change any
protection that your file has. If there is protection and that protection
includes passwords, include the passwords or change the passwords or delete
the passwords. But make sure that your problems still exist on the file you
send.
Also tell me the version of Excel that you are running.
My email address is . Remove the "nop" from this
address. HTH Otto
"diaare" wrote in message
...
Thanks for your response. I was happy see that the change you made was
what
I was going to do, I guess know more VBA than I thought.

Unfortunately, when I try to run it brings my system to a halt, until I
finally have to close Excel through Task Manager. Any ideas on what I
could
do to speed it up? If not maybe I'll just have to continue on with the
manual process...

Thanks,
Diane

"Otto Moehrbach" wrote:

Diane
Try this. I also added some code to freeze the screen so you won't
see
things jumping around as cells are deleted. The jumping around (called
"painting the screen") takes a lot of time so this way the code will run
faster. HTH Otto
Sub DeleteCells()
Dim c As Long
Dim d As Long
Dim TheRng As Range
Application.ScreenUpdating = False
For c = 3 To 24 Step 3
Set TheRng = Range(Cells(2, c), Cells(Rows.Count,
c).End(xlUp))
For d = TheRng.Count To 1 Step -1
If TheRng(d).Value = "" And
TheRng(d).Offset(, -2).Value =
"" Then _
TheRng(d).Offset(, -2).Resize(, 3).Delete
Shift:=xlUp
Next d
Next c
Application.ScreenUpdating = True
End Sub
"diaare" wrote in message
...
Thnaks for the response. You were correct in assuming I meant Left of
the
cell. My fingers don't always type what I mean.

I pasted your macro in and recieved an error on Shift:=xlUp I changed
it
to
Shift = xlUp and it runs now. And it does exacly what I asked for,
unfortunately I didn't think through my question entirely...

Here is the problem, I need to add another condition to this rule if
possible. (I didn't think about it until I saw it run).

If there is a value in the cell 2 cells to the left of the blank, I do
not
want it to delete the cells in that row. So, really what I want is to
find
where cells in the totals column that are blank, and if the cell two to
the
left are blank as well, then delete those two cells and the one between
them.

For example:

A B C D E F
1 x 5 x x 3
2 x x 4
3 4 x x
4 x 6 5 x

I would only want to delete A2, B2, C2 and D3, E3, F3

This is proving to be more complicated than I thought...is it still
doable?

Thnaks again for your help.

"Otto Moehrbach" wrote:

Diane
Don't you mean "two cells to the LEFT"? Your explanation says to
the
right but your example shows to the left.
The macro would look like this:
Sub DeleteCells()
Dim c As Long
Dim d As Long
Dim TheRng As Range
For c = 3 To 24 Step 3
Set TheRng = Range(Cells(2, c), Cells(Rows.Count,
c).End(xlUp))
For d = TheRng.Count To 1 Step -1
If TheRng(d).Value = "" Then _
TheRng(d).Offset(, -2).Resize(, 3).Delete
Shift:=xlUp
Next d
Next c
End Sub
Please post back if you need more. HTH Otto
"diaare" wrote in message
...
I have a file that comes in weekly that I take numerous steps to
clean
up
the
data for my reports. I am hoping to automate some of these. Could
one
of
you experts help me to create a macro to do the following:

Every third column (beginning with C thru X) contains SUMIF formulas
to
calculate totals - ie: =IF(SUMIF(Breakdown!D:D,T6,Breakdown!C:C)<1,"
",SUMIF(Breakdown!D:D,T6,Breakdown!C:C)).

If the formula results in a blank, then I need to delete that cell
and
the
two cells to the right of it, and move the rest of the cells up.

For example, if C3 and C6 are blank then I need Cells A3, B3, C3,
A6,
B6,
and C6 to be deleted and all existing cells in those columns to move
up.
This needs to be done for all the blank cells in the totals columns
stated
above.

I have some experience with macros and VBA, but I need some help
getting
started on this one....

Thanks,
Diane

Diane
When you get an error message in Excel, the box that shows the error has
a button labeled "Debug". Click on that button and Excel will take you to
the code and will highlight the offending line of code. What does the error
message say and what is the offending line of code? HTH Otto
"diaare" wrote in message
...
Okay - I have multiple macros in this file, so to troubleshoot:

I copied my data into a new workbook and only put in your macro. When I
try
to run it I get an error popup that is a big red x with "400" as the
message.

We are on a network, but I have created many other macros that run without
a
problem, so I don't think it is an IT issue.

In the meantime, I am going to try and get with someone here and see if
they
can help diagnose the problem. I appreciate your offer to look at the
file,
but I know that sending any attachements out is frowned upon here, so I
will
use it as a last resort.

Thanks so much for all of your help,
Diane

"Otto Moehrbach" wrote:

Diane
I have Excel XP, also known as 2002. I made up some dummy data in a
file and ran the code I gave you and it works just fine. I can't point
to
any one thing that might be causing your problems, but here are some
things
you might look at.
Do you have a module and a macro by the same name? Excel tends to hang
up
when this happens.
Do you have multiple macros by the same name? This usually causes an
error
message and you didn't say you had an error message.
Are you running this file within a network containing main servers at
your
work? Perhaps your IT people have inserted a block into the system to
stop
all automation that hasn't been cleared by them first.
Short of looking at your file, there is not much I can do. The code I
gave
you is in no way complex or strange. It should run without any problem
at
all.
If you wish, send me your file via email (don't post your file in the
newsgroup) and I'll look at it. Fake the data as you wish to protect it,
I
need only the layout of the data and the file. Make sure that your
problems
can be repeated on the file you send. That's important. Also, be sure
to
send the file as it is when you experience the problem. In other words,
don't delete any code or modules or sheets. Don't delete or change any
protection that your file has. If there is protection and that
protection
includes passwords, include the passwords or change the passwords or
delete
the passwords. But make sure that your problems still exist on the file
you
send.
Also tell me the version of Excel that you are running.
My email address is . Remove the "nop" from this
address. HTH Otto
"diaare" wrote in message
...
Thanks for your response. I was happy see that the change you made was
what
I was going to do, I guess know more VBA than I thought.

Unfortunately, when I try to run it brings my system to a halt, until I
finally have to close Excel through Task Manager. Any ideas on what I
could
do to speed it up? If not maybe I'll just have to continue on with the
manual process...

Thanks,
Diane

"Otto Moehrbach" wrote:

Diane
Try this. I also added some code to freeze the screen so you
won't
see
things jumping around as cells are deleted. The jumping around
(called
"painting the screen") takes a lot of time so this way the code will
run
faster. HTH Otto
Sub DeleteCells()
Dim c As Long
Dim d As Long
Dim TheRng As Range
Application.ScreenUpdating = False
For c = 3 To 24 Step 3
Set TheRng = Range(Cells(2, c), Cells(Rows.Count,
c).End(xlUp))
For d = TheRng.Count To 1 Step -1
If TheRng(d).Value = "" And
TheRng(d).Offset(, -2).Value =
"" Then _
TheRng(d).Offset(, -2).Resize(, 3).Delete
Shift:=xlUp
Next d
Next c
Application.ScreenUpdating = True
End Sub
"diaare" wrote in message
...
Thnaks for the response. You were correct in assuming I meant Left
of
the
cell. My fingers don't always type what I mean.

I pasted your macro in and recieved an error on Shift:=xlUp I
changed
it
to
Shift = xlUp and it runs now. And it does exacly what I asked for,
unfortunately I didn't think through my question entirely...

Here is the problem, I need to add another condition to this rule if
possible. (I didn't think about it until I saw it run).

If there is a value in the cell 2 cells to the left of the blank, I
do
not
want it to delete the cells in that row. So, really what I want is
to
find
where cells in the totals column that are blank, and if the cell two
to
the
left are blank as well, then delete those two cells and the one
between
them.

For example:

A B C D E F
1 x 5 x x 3
2 x x 4
3 4 x x
4 x 6 5 x

I would only want to delete A2, B2, C2 and D3, E3, F3

This is proving to be more complicated than I thought...is it still
doable?

Thnaks again for your help.

"Otto Moehrbach" wrote:

Diane
Don't you mean "two cells to the LEFT"? Your explanation says
to
the
right but your example shows to the left.
The macro would look like this:
Sub DeleteCells()
Dim c As Long
Dim d As Long
Dim TheRng As Range
For c = 3 To 24 Step 3
Set TheRng = Range(Cells(2, c), Cells(Rows.Count,
c).End(xlUp))
For d = TheRng.Count To 1 Step -1
If TheRng(d).Value = "" Then _
TheRng(d).Offset(, -2).Resize(, 3).Delete
Shift:=xlUp
Next d
Next c
End Sub
Please post back if you need more. HTH Otto
"diaare" wrote in message
...
I have a file that comes in weekly that I take numerous steps to
clean
up
the
data for my reports. I am hoping to automate some of these.
Could
one
of
you experts help me to create a macro to do the following:

Every third column (beginning with C thru X) contains SUMIF
formulas
to
calculate totals - ie:
=IF(SUMIF(Breakdown!D:D,T6,Breakdown!C:C)<1,"
",SUMIF(Breakdown!D:D,T6,Breakdown!C:C)).

If the formula results in a blank, then I need to delete that
cell
and
the
two cells to the right of it, and move the rest of the cells up.

For example, if C3 and C6 are blank then I need Cells A3, B3, C3,
A6,
B6,
and C6 to be deleted and all existing cells in those columns to
move
up.
This needs to be done for all the blank cells in the totals
columns
stated
above.

I have some experience with macros and VBA, but I need some help
getting
started on this one....

Thanks,
Diane

Yeah..this message was different. The only option I had was OK. It didn't
have the normal error message header either...just a big red x and the text
"400"

I ended up clearing out all of my macros, saving the file, and adding them
back in one at a time, checking to be sure they would run as I went.
Eventually they were all added back in and are working properly.

Thanks again for all of your help. This will save me a whole lot of time
each week.

Diane

"Otto Moehrbach" wrote:

Diane
When you get an error message in Excel, the box that shows the error has
a button labeled "Debug". Click on that button and Excel will take you to
the code and will highlight the offending line of code. What does the error
message say and what is the offending line of code? HTH Otto
"diaare" wrote in message
...
Okay - I have multiple macros in this file, so to troubleshoot:

I copied my data into a new workbook and only put in your macro. When I
try
to run it I get an error popup that is a big red x with "400" as the
message.

We are on a network, but I have created many other macros that run without
a
problem, so I don't think it is an IT issue.

In the meantime, I am going to try and get with someone here and see if
they
can help diagnose the problem. I appreciate your offer to look at the
file,
but I know that sending any attachements out is frowned upon here, so I
will
use it as a last resort.

Thanks so much for all of your help,
Diane

"Otto Moehrbach" wrote:

Diane
I have Excel XP, also known as 2002. I made up some dummy data in a
file and ran the code I gave you and it works just fine. I can't point
to
any one thing that might be causing your problems, but here are some
things
you might look at.
Do you have a module and a macro by the same name? Excel tends to hang
up
when this happens.
Do you have multiple macros by the same name? This usually causes an
error
message and you didn't say you had an error message.
Are you running this file within a network containing main servers at
your
work? Perhaps your IT people have inserted a block into the system to
stop
all automation that hasn't been cleared by them first.
Short of looking at your file, there is not much I can do. The code I
gave
you is in no way complex or strange. It should run without any problem
at
all.
If you wish, send me your file via email (don't post your file in the
newsgroup) and I'll look at it. Fake the data as you wish to protect it,
I
need only the layout of the data and the file. Make sure that your
problems
can be repeated on the file you send. That's important. Also, be sure
to
send the file as it is when you experience the problem. In other words,
don't delete any code or modules or sheets. Don't delete or change any
protection that your file has. If there is protection and that
protection
includes passwords, include the passwords or change the passwords or
delete
the passwords. But make sure that your problems still exist on the file
you
send.
Also tell me the version of Excel that you are running.
My email address is . Remove the "nop" from this
address. HTH Otto
"diaare" wrote in message
...
Thanks for your response. I was happy see that the change you made was
what
I was going to do, I guess know more VBA than I thought.

Unfortunately, when I try to run it brings my system to a halt, until I
finally have to close Excel through Task Manager. Any ideas on what I
could
do to speed it up? If not maybe I'll just have to continue on with the
manual process...

Thanks,
Diane

"Otto Moehrbach" wrote:

Diane
Try this. I also added some code to freeze the screen so you
won't
see
things jumping around as cells are deleted. The jumping around
(called
"painting the screen") takes a lot of time so this way the code will
run
faster. HTH Otto
Sub DeleteCells()
Dim c As Long
Dim d As Long
Dim TheRng As Range
Application.ScreenUpdating = False
For c = 3 To 24 Step 3
Set TheRng = Range(Cells(2, c), Cells(Rows.Count,
c).End(xlUp))
For d = TheRng.Count To 1 Step -1
If TheRng(d).Value = "" And
TheRng(d).Offset(, -2).Value =
"" Then _
TheRng(d).Offset(, -2).Resize(, 3).Delete
Shift:=xlUp
Next d
Next c
Application.ScreenUpdating = True
End Sub
"diaare" wrote in message
...
Thnaks for the response. You were correct in assuming I meant Left
of
the
cell. My fingers don't always type what I mean.

I pasted your macro in and recieved an error on Shift:=xlUp I
changed
it
to
Shift = xlUp and it runs now. And it does exacly what I asked for,
unfortunately I didn't think through my question entirely...

Here is the problem, I need to add another condition to this rule if
possible. (I didn't think about it until I saw it run).

If there is a value in the cell 2 cells to the left of the blank, I
do
not
want it to delete the cells in that row. So, really what I want is
to
find
where cells in the totals column that are blank, and if the cell two
to
the
left are blank as well, then delete those two cells and the one
between
them.

For example:

A B C D E F
1 x 5 x x 3
2 x x 4
3 4 x x
4 x 6 5 x

I would only want to delete A2, B2, C2 and D3, E3, F3

This is proving to be more complicated than I thought...is it still
doable?

Thnaks again for your help.

"Otto Moehrbach" wrote:

Diane
Don't you mean "two cells to the LEFT"? Your explanation says
to
the
right but your example shows to the left.
The macro would look like this:
Sub DeleteCells()
Dim c As Long
Dim d As Long
Dim TheRng As Range
For c = 3 To 24 Step 3
Set TheRng = Range(Cells(2, c), Cells(Rows.Count,
c).End(xlUp))
For d = TheRng.Count To 1 Step -1
If TheRng(d).Value = "" Then _
TheRng(d).Offset(, -2).Resize(, 3).Delete
Shift:=xlUp
Next d
Next c
End Sub
Please post back if you need more. HTH Otto
"diaare" wrote in message
...
I have a file that comes in weekly that I take numerous steps to
clean
up
the
data for my reports. I am hoping to automate some of these.
Could
one
of
you experts help me to create a macro to do the following:

Every third column (beginning with C thru X) contains SUMIF
formulas
to
calculate totals - ie:
=IF(SUMIF(Breakdown!D:D,T6,Breakdown!C:C)<1,"
",SUMIF(Breakdown!D:D,T6,Breakdown!C:C)).

If the formula results in a blank, then I need to delete that
cell
and
the
two cells to the right of it, and move the rest of the cells up.

For example, if C3 and C6 are blank then I need Cells A3, B3, C3,
A6,
B6,
and C6 to be deleted and all existing cells in those columns to
move
up.
This needs to be done for all the blank cells in the totals
columns
stated
above.

I have some experience with macros and VBA, but I need some help
getting
started on this one....

Thanks,
Diane