Calcutate SUM of column in first blank row
 

Hi All,

Hope someone can help me with this.

I am trying to find a method of finding the first blank row on a worksheet
and perform a sum calculation for column B:

example:
Before
A B
ww ww
x 1
x 2
x 5
x 6

After
ww ww
A B
x 1
x 2
x 5
x 6
14

I have code to find the first blank row:
Dim NextRow As Long
NextRow = Range("B65536").End(xlUp).Row + 1
Cells(NextRow, 2) = "test"

But of course, I don't want it to say test, but insteade calulate the sum of
all values in the column from B2 down (B1 has heading)

Thank you, really appreciate any help

Calcutate SUM of column in first blank row
 

Hi,

Like this

LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Cells(LastRow + 1, 2).Formula = "=Sum(B2:B" & LastRow & ")"
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PVANS" wrote:

Hi All,

Hope someone can help me with this.

I am trying to find a method of finding the first blank row on a worksheet
and perform a sum calculation for column B:

example:
Before
A B
ww ww
x 1
x 2
x 5
x 6

After
ww ww
A B
x 1
x 2
x 5
x 6
14

I have code to find the first blank row:
Dim NextRow As Long
NextRow = Range("B65536").End(xlUp).Row + 1
Cells(NextRow, 2) = "test"

But of course, I don't want it to say test, but insteade calulate the sum of
all values in the column from B2 down (B1 has heading)

Thank you, really appreciate any help

Calcutate SUM of column in first blank row
 

Mike,

Can you explain what the "B" represents in your first line? Doesnt the
argument for Cells have to be (<integer,<integer)? I'm confused.

Thanks.

-Dom

"Mike H" wrote:

Hi,

Like this

LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Cells(LastRow + 1, 2).Formula = "=Sum(B2:B" & LastRow & ")"
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PVANS" wrote:

Hi All,

Hope someone can help me with this.

I am trying to find a method of finding the first blank row on a worksheet
and perform a sum calculation for column B:

example:
Before
A B
ww ww
x 1
x 2
x 5
x 6

After
ww ww
A B
x 1
x 2
x 5
x 6
14

I have code to find the first blank row:
Dim NextRow As Long
NextRow = Range("B65536").End(xlUp).Row + 1
Cells(NextRow, 2) = "test"

But of course, I don't want it to say test, but insteade calulate the sum of
all values in the column from B2 down (B1 has heading)

Thank you, really appreciate any help

Calcutate SUM of column in first blank row
 

Can you explain what the "B" represents in your first line?

LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row

The B represent column B and no the 2 values for 'CELL' don't have to be
integer

Cells(1, "B").Value = "wwwww"
is the same as
Cells(1, 2).Value = "wwwww"

HTH


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Domenick" wrote:

Mike,

Can you explain what the "B" represents in your first line? Doesnt the
argument for Cells have to be (<integer,<integer)? I'm confused.

Thanks.

-Dom

"Mike H" wrote:

Hi,

Like this

LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Cells(LastRow + 1, 2).Formula = "=Sum(B2:B" & LastRow & ")"
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PVANS" wrote:

Hi All,

Hope someone can help me with this.

I am trying to find a method of finding the first blank row on a worksheet
and perform a sum calculation for column B:

example:
Before
A B
ww ww
x 1
x 2
x 5
x 6

After
ww ww
A B
x 1
x 2
x 5
x 6
14

I have code to find the first blank row:
Dim NextRow As Long
NextRow = Range("B65536").End(xlUp).Row + 1
Cells(NextRow, 2) = "test"

But of course, I don't want it to say test, but insteade calulate the sum of
all values in the column from B2 down (B1 has heading)

Thank you, really appreciate any help

Calcutate SUM of column in first blank row
 

Actually looking at it my syntax was mixed

I could have been consistent by using

LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Cells(LastRow + 1, "B").Formula = "=Sum(B2:B" & LastRow & ")"

or

LastRow = Cells(Cells.Rows.Count, 2).End(xlUp).Row
Cells(LastRow + 1, 2).Formula = "=Sum(B2:B" & LastRow & ")"

But I mixed to 2 different methods together and i wasn't aware I habitually
did that until now.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Can you explain what the "B" represents in your first line?

LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row

The B represent column B and no the 2 values for 'CELL' don't have to be
integer

Cells(1, "B").Value = "wwwww"
is the same as
Cells(1, 2).Value = "wwwww"

HTH


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Domenick" wrote:

Mike,

Can you explain what the "B" represents in your first line? Doesnt the
argument for Cells have to be (<integer,<integer)? I'm confused.

Thanks.

-Dom

"Mike H" wrote:

Hi,

Like this

LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Cells(LastRow + 1, 2).Formula = "=Sum(B2:B" & LastRow & ")"
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PVANS" wrote:

Hi All,

Hope someone can help me with this.

I am trying to find a method of finding the first blank row on a worksheet
and perform a sum calculation for column B:

example:
Before
A B
ww ww
x 1
x 2
x 5
x 6

After
ww ww
A B
x 1
x 2
x 5
x 6
14

I have code to find the first blank row:
Dim NextRow As Long
NextRow = Range("B65536").End(xlUp).Row + 1
Cells(NextRow, 2) = "test"

But of course, I don't want it to say test, but insteade calulate the sum of
all values in the column from B2 down (B1 has heading)

Thank you, really appreciate any help

Calcutate SUM of column in first blank row
 

Ahhh.. It is crystal clear now! Thanks for the clarification.

-Dom

"Mike H" wrote:

Actually looking at it my syntax was mixed

I could have been consistent by using

LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Cells(LastRow + 1, "B").Formula = "=Sum(B2:B" & LastRow & ")"

or

LastRow = Cells(Cells.Rows.Count, 2).End(xlUp).Row
Cells(LastRow + 1, 2).Formula = "=Sum(B2:B" & LastRow & ")"

But I mixed to 2 different methods together and i wasn't aware I habitually
did that until now.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Can you explain what the "B" represents in your first line?

LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row

The B represent column B and no the 2 values for 'CELL' don't have to be
integer

Cells(1, "B").Value = "wwwww"
is the same as
Cells(1, 2).Value = "wwwww"

HTH


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Domenick" wrote:

Mike,

Can you explain what the "B" represents in your first line? Doesnt the
argument for Cells have to be (<integer,<integer)? I'm confused.

Thanks.

-Dom

"Mike H" wrote:

Hi,

Like this

LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Cells(LastRow + 1, 2).Formula = "=Sum(B2:B" & LastRow & ")"
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PVANS" wrote:

Hi All,

Hope someone can help me with this.

I am trying to find a method of finding the first blank row on a worksheet
and perform a sum calculation for column B:

example:
Before
A B
ww ww
x 1
x 2
x 5
x 6

After
ww ww
A B
x 1
x 2
x 5
x 6
14

I have code to find the first blank row:
Dim NextRow As Long
NextRow = Range("B65536").End(xlUp).Row + 1
Cells(NextRow, 2) = "test"

But of course, I don't want it to say test, but insteade calulate the sum of
all values in the column from B2 down (B1 has heading)

Thank you, really appreciate any help