Calcutate SUM of column in first blank row

  • Thread starter Thread starter PVANS
  • Start date Start date
P

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
 
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.
 
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
 
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.
 
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.
 
Back
Top