A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart, like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12, A14,
etc. I'd like to put the total in the first cell (A4), and delete the other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this time.

Thanks for any solutions. You guys are amazing.

Ron

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart, like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12,
A14,
etc. I'd like to put the total in the first cell (A4), and delete the
other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this time.

Thanks for any solutions. You guys are amazing.

Ron

The first one deleted eveything in my column.

It looked like yours was stepping through, until it actually had to add
something.

The data in the cells is time in general format. Should add fine.

Again, I may go the entire file with no cells to add. (2 rows apart). And
then there may be 6 in a row that are 2 rows apart.
A1 24
A2
A3
A4 12 (need to add A4, A6, A8, A10 in this case) Would like the sum of all 4
in
A5 A4, if possible, with A6, A8, A10 cleared.
A6 23
A7
A8 11
A9
A10 14
A11
A12
A13
A14

Thanks for all the efforts.

Ron


"Don Guillett" wrote:

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart, like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12,
A14,
etc. I'd like to put the total in the first cell (A4), and delete the
other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this time.

Thanks for any solutions. You guys are amazing.

Ron

I'm not sure that the last row qualifies but try this to get 60 from your
sample.

Sub countifspaces1()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & lr)
If c.Offset(2) 0 Then ms = ms + c
Next c
MsgBox ms + Range("a" & lr)
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
The first one deleted eveything in my column.

It looked like yours was stepping through, until it actually had to add
something.

The data in the cells is time in general format. Should add fine.

Again, I may go the entire file with no cells to add. (2 rows apart). And
then there may be 6 in a row that are 2 rows apart.
A1 24
A2
A3
A4 12 (need to add A4, A6, A8, A10 in this case) Would like the sum of all
4
in
A5 A4, if possible, with A6, A8, A10 cleared.
A6 23
A7
A8 11
A9
A10 14
A11
A12
A13
A14

Thanks for all the efforts.

Ron


"Don Guillett" wrote:

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart,
like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12,
A14,
etc. I'd like to put the total in the first cell (A4), and delete the
other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this
time.

Thanks for any solutions. You guys are amazing.

Ron

Don,

I'm getting close. I'm adding the values, when the offset is 2, but how do I
clear the cell from the first value and write the sum in the second cell, or
a cell next to it?

If A4 and A6 have values, then I want to clear A4 and write the sum in A7
(or I'd settle for B6 (clearing both A4 and A6).

And if there are more, then I need to sum the next offset 2 value, again
clearing the last one...and if I wrote the sum in B7, then that would need to
be cleared also.

I'm just not good at storing and writing cell values.

Thanks,

Ron

"Don Guillett" wrote:

I'm not sure that the last row qualifies but try this to get 60 from your
sample.

Sub countifspaces1()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & lr)
If c.Offset(2) 0 Then ms = ms + c
Next c
MsgBox ms + Range("a" & lr)
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
The first one deleted eveything in my column.

It looked like yours was stepping through, until it actually had to add
something.

The data in the cells is time in general format. Should add fine.

Again, I may go the entire file with no cells to add. (2 rows apart). And
then there may be 6 in a row that are 2 rows apart.
A1 24
A2
A3
A4 12 (need to add A4, A6, A8, A10 in this case) Would like the sum of all
4
in
A5 A4, if possible, with A6, A8, A10 cleared.
A6 23
A7
A8 11
A9
A10 14
A11
A12
A13
A14

Thanks for all the efforts.

Ron


"Don Guillett" wrote:

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart,
like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12,
A14,
etc. I'd like to put the total in the first cell (A4), and delete the
other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this
time.

Thanks for any solutions. You guys are amazing.

Ron

I'm having a hard time trying to visualize what you want. Send your file to
my address below along with this msg and before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
Don,

I'm getting close. I'm adding the values, when the offset is 2, but how do
I
clear the cell from the first value and write the sum in the second cell,
or
a cell next to it?

If A4 and A6 have values, then I want to clear A4 and write the sum in A7
(or I'd settle for B6 (clearing both A4 and A6).

And if there are more, then I need to sum the next offset 2 value, again
clearing the last one...and if I wrote the sum in B7, then that would need
to
be cleared also.

I'm just not good at storing and writing cell values.

Thanks,

Ron

"Don Guillett" wrote:

I'm not sure that the last row qualifies but try this to get 60 from your
sample.

Sub countifspaces1()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & lr)
If c.Offset(2) 0 Then ms = ms + c
Next c
MsgBox ms + Range("a" & lr)
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
The first one deleted eveything in my column.

It looked like yours was stepping through, until it actually had to add
something.

The data in the cells is time in general format. Should add fine.

Again, I may go the entire file with no cells to add. (2 rows apart).
And
then there may be 6 in a row that are 2 rows apart.
A1 24
A2
A3
A4 12 (need to add A4, A6, A8, A10 in this case) Would like the sum of
all
4
in
A5 A4, if possible, with A6, A8, A10 cleared.
A6 23
A7
A8 11
A9
A10 14
A11
A12
A13
A14

Thanks for all the efforts.

Ron


"Don Guillett" wrote:

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells
apart,
like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10,
A12,
A14,
etc. I'd like to put the total in the first cell (A4), and delete
the
other
values..in A6, etc, but I'd settle for any solution. I am so tired
of
browsing posts for this. They usually get me through, but not this
time.

Thanks for any solutions. You guys are amazing.

Ron

I'm having a hard time trying to visualize what you want. Send your file to
my address below along with this msg and before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
Don,

I'm getting close. I'm adding the values, when the offset is 2, but how do
I
clear the cell from the first value and write the sum in the second cell,
or
a cell next to it?

If A4 and A6 have values, then I want to clear A4 and write the sum in A7
(or I'd settle for B6 (clearing both A4 and A6).

And if there are more, then I need to sum the next offset 2 value, again
clearing the last one...and if I wrote the sum in B7, then that would need
to
be cleared also.

I'm just not good at storing and writing cell values.

Thanks,

Ron

"Don Guillett" wrote:

I'm not sure that the last row qualifies but try this to get 60 from your
sample.

Sub countifspaces1()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & lr)
If c.Offset(2) 0 Then ms = ms + c
Next c
MsgBox ms + Range("a" & lr)
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
The first one deleted eveything in my column.

It looked like yours was stepping through, until it actually had to add
something.

The data in the cells is time in general format. Should add fine.

Again, I may go the entire file with no cells to add. (2 rows apart).
And
then there may be 6 in a row that are 2 rows apart.
A1 24
A2
A3
A4 12 (need to add A4, A6, A8, A10 in this case) Would like the sum of
all
4
in
A5 A4, if possible, with A6, A8, A10 cleared.
A6 23
A7
A8 11
A9
A10 14
A11
A12
A13
A14

Thanks for all the efforts.

Ron


"Don Guillett" wrote:

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells
apart,
like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10,
A12,
A14,
etc. I'd like to put the total in the first cell (A4), and delete
the
other
values..in A6, etc, but I'd settle for any solution. I am so tired
of
browsing posts for this. They usually get me through, but not this
time.

Thanks for any solutions. You guys are amazing.

Ron

Don,

I'm getting close. I'm adding the values, when the offset is 2, but how do I
clear the cell from the first value and write the sum in the second cell, or
a cell next to it?

If A4 and A6 have values, then I want to clear A4 and write the sum in A7
(or I'd settle for B6 (clearing both A4 and A6).

And if there are more, then I need to sum the next offset 2 value, again
clearing the last one...and if I wrote the sum in B7, then that would need to
be cleared also.

I'm just not good at storing and writing cell values.

Thanks,

Ron

"Don Guillett" wrote:

I'm not sure that the last row qualifies but try this to get 60 from your
sample.

Sub countifspaces1()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & lr)
If c.Offset(2) 0 Then ms = ms + c
Next c
MsgBox ms + Range("a" & lr)
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
The first one deleted eveything in my column.

It looked like yours was stepping through, until it actually had to add
something.

The data in the cells is time in general format. Should add fine.

Again, I may go the entire file with no cells to add. (2 rows apart). And
then there may be 6 in a row that are 2 rows apart.
A1 24
A2
A3
A4 12 (need to add A4, A6, A8, A10 in this case) Would like the sum of all
4
in
A5 A4, if possible, with A6, A8, A10 cleared.
A6 23
A7
A8 11
A9
A10 14
A11
A12
A13
A14

Thanks for all the efforts.

Ron


"Don Guillett" wrote:

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart,
like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12,
A14,
etc. I'd like to put the total in the first cell (A4), and delete the
other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this
time.

Thanks for any solutions. You guys are amazing.

Ron

what does not work in my solution?

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Ron" wrote:

The first one deleted eveything in my column.

It looked like yours was stepping through, until it actually had to add
something.

The data in the cells is time in general format. Should add fine.

Again, I may go the entire file with no cells to add. (2 rows apart). And
then there may be 6 in a row that are 2 rows apart.
A1 24
A2
A3
A4 12 (need to add A4, A6, A8, A10 in this case) Would like the sum of all 4
in
A5 A4, if possible, with A6, A8, A10 cleared.
A6 23
A7
A8 11
A9
A10 14
A11
A12
A13
A14

Thanks for all the efforts.

Ron


"Don Guillett" wrote:

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart, like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12,
A14,
etc. I'd like to put the total in the first cell (A4), and delete the
other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this time.

Thanks for any solutions. You guys are amazing.

Ron

It deleted eveything in my column.

Ron

"r" wrote:

what does not work in my solution?

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Ron" wrote:

The first one deleted eveything in my column.

It looked like yours was stepping through, until it actually had to add
something.

The data in the cells is time in general format. Should add fine.

Again, I may go the entire file with no cells to add. (2 rows apart). And
then there may be 6 in a row that are 2 rows apart.
A1 24
A2
A3
A4 12 (need to add A4, A6, A8, A10 in this case) Would like the sum of all 4
in
A5 A4, if possible, with A6, A8, A10 cleared.
A6 23
A7
A8 11
A9
A10 14
A11
A12
A13
A14

Thanks for all the efforts.

Ron


"Don Guillett" wrote:

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart, like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12,
A14,
etc. I'd like to put the total in the first cell (A4), and delete the
other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this time.

Thanks for any solutions. You guys are amazing.

Ron

That's what I understood you want
sitation
a1=24
a2=""
a3=""
a4=12
a5=""
a6=23
a7=""
a8=10
.....

result
a1=24
a2=""
a3=""
a4=45
a5=""
a6=""
a7=""
a8=""
....
but I do not understand anything ...
I am sorry but my English sometimes betrays
so I withdrawal
regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Ron" wrote:

It deleted eveything in my column.

Ron

"r" wrote:

what does not work in my solution?

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Ron" wrote:

The first one deleted eveything in my column.

It looked like yours was stepping through, until it actually had to add
something.

The data in the cells is time in general format. Should add fine.

Again, I may go the entire file with no cells to add. (2 rows apart). And
then there may be 6 in a row that are 2 rows apart.
A1 24
A2
A3
A4 12 (need to add A4, A6, A8, A10 in this case) Would like the sum of all 4
in
A5 A4, if possible, with A6, A8, A10 cleared.
A6 23
A7
A8 11
A9
A10 14
A11
A12
A13
A14

Thanks for all the efforts.

Ron


"Don Guillett" wrote:

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart, like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12,
A14,
etc. I'd like to put the total in the first cell (A4), and delete the
other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this time.

Thanks for any solutions. You guys are amazing.

Ron

That's what I understood you want
sitation
a1=24
a2=""
a3=""
a4=12
a5=""
a6=23
a7=""
a8=10
.....

result
a1=24
a2=""
a3=""
a4=45
a5=""
a6=""
a7=""
a8=""
....
but I do not understand anything ...
I am sorry but my English sometimes betrays
so I withdrawal
regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Ron" wrote:

It deleted eveything in my column.

Ron

"r" wrote:

what does not work in my solution?

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Ron" wrote:

The first one deleted eveything in my column.

It looked like yours was stepping through, until it actually had to add
something.

The data in the cells is time in general format. Should add fine.

Again, I may go the entire file with no cells to add. (2 rows apart). And
then there may be 6 in a row that are 2 rows apart.
A1 24
A2
A3
A4 12 (need to add A4, A6, A8, A10 in this case) Would like the sum of all 4
in
A5 A4, if possible, with A6, A8, A10 cleared.
A6 23
A7
A8 11
A9
A10 14
A11
A12
A13
A14

Thanks for all the efforts.

Ron


"Don Guillett" wrote:

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart, like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12,
A14,
etc. I'd like to put the total in the first cell (A4), and delete the
other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this time.

Thanks for any solutions. You guys are amazing.

Ron

It deleted eveything in my column.

Ron

"r" wrote:

what does not work in my solution?

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Ron" wrote:

The first one deleted eveything in my column.

It looked like yours was stepping through, until it actually had to add
something.

The data in the cells is time in general format. Should add fine.

Again, I may go the entire file with no cells to add. (2 rows apart). And
then there may be 6 in a row that are 2 rows apart.
A1 24
A2
A3
A4 12 (need to add A4, A6, A8, A10 in this case) Would like the sum of all 4
in
A5 A4, if possible, with A6, A8, A10 cleared.
A6 23
A7
A8 11
A9
A10 14
A11
A12
A13
A14

Thanks for all the efforts.

Ron


"Don Guillett" wrote:

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart, like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12,
A14,
etc. I'd like to put the total in the first cell (A4), and delete the
other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this time.

Thanks for any solutions. You guys are amazing.

Ron

I'm not sure that the last row qualifies but try this to get 60 from your
sample.

Sub countifspaces1()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & lr)
If c.Offset(2) 0 Then ms = ms + c
Next c
MsgBox ms + Range("a" & lr)
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
The first one deleted eveything in my column.

It looked like yours was stepping through, until it actually had to add
something.

The data in the cells is time in general format. Should add fine.

Again, I may go the entire file with no cells to add. (2 rows apart). And
then there may be 6 in a row that are 2 rows apart.
A1 24
A2
A3
A4 12 (need to add A4, A6, A8, A10 in this case) Would like the sum of all
4
in
A5 A4, if possible, with A6, A8, A10 cleared.
A6 23
A7
A8 11
A9
A10 14
A11
A12
A13
A14

Thanks for all the efforts.

Ron


"Don Guillett" wrote:

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart,
like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12,
A14,
etc. I'd like to put the total in the first cell (A4), and delete the
other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this
time.

Thanks for any solutions. You guys are amazing.

Ron

The first one deleted eveything in my column.

It looked like yours was stepping through, until it actually had to add
something.

The data in the cells is time in general format. Should add fine.

Again, I may go the entire file with no cells to add. (2 rows apart). And
then there may be 6 in a row that are 2 rows apart.
A1 24
A2
A3
A4 12 (need to add A4, A6, A8, A10 in this case) Would like the sum of all 4
in
A5 A4, if possible, with A6, A8, A10 cleared.
A6 23
A7
A8 11
A9
A10 14
A11
A12
A13
A14

Thanks for all the efforts.

Ron


"Don Guillett" wrote:

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart, like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12,
A14,
etc. I'd like to put the total in the first cell (A4), and delete the
other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this time.

Thanks for any solutions. You guys are amazing.

Ron

Sub test()
s [a1]
End Sub

Sub s(rng As Excel.Range)
Dim l As Long, t, v
Dim rngR As Excel.Range
t = rng.EntireColumn.Cells(1)
For l = 2 To rng.EntireColumn.Cells.Count
If Not IsEmpty(rng.EntireColumn.Cells(l)) Then
Set rngR = rng.EntireColumn.Cells(l)
Exit For
End If
Next
v = Application.WorksheetFunction.Sum(rng.EntireColumn )
rng.EntireColumn.ClearContents
rngR.Value = v - t
rng.EntireColumn.Cells(1) = t

End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Ron" wrote:

A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart, like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12, A14,
etc. I'd like to put the total in the first cell (A4), and delete the other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this time.

Thanks for any solutions. You guys are amazing.

Ron

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ron" wrote in message
...
A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart, like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12,
A14,
etc. I'd like to put the total in the first cell (A4), and delete the
other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this time.

Thanks for any solutions. You guys are amazing.

Ron