Calcutate SUM of column in first blank row

[PVANS]

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

 

[Mike H]

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.

 

[Domenick]

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]

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.

 

[Mike H]

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.

 

[Domenick]

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

-Dom