Find last empty row

P

poppy

Hi

I made a mess of my last post, but here goes.

I would like to be able to find the last empty row in a worksheet ski
one row and then sum up the totals of each column in this row.

I hope this is clearer than my last post
Thanx for your help

Kind Regard
 
N

Nigel

The following inserts a formula in the last empty row of a column and sums
all values.
You can adpat this for the column, range etc as required

Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(lastrow + 1, 1).Formula = "=sum(A1:A" & lastrow & ")"


Cheers
Nigel
 
C

Chris.F

Have you thought about putting the totals at the top of your data rather
than at the bottom. Then the formula can sum right down to 65000 if you
want, and no need for a macro at all.

Chris
 
D

Dave Peterson

When I put the totals at the top, I'll also use the =subtotal() function. Then
when I apply data|Filter|autofilter, I get to see the subtotals for the visible
rows.
 
P

poppy

Thanx Nigel

I works fine except for one small detail. When I run the macro for
second time it gives me problems. I think this is becuase I do a fil
function and I have hardcoded the destination fill. Does that mak
sense to you?


Code
-------------------
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Cells(lastrow + 1, 1) = "TOTAL"
Cells(lastrow + 1, 2).Formula = "=sum(B3:B" & lastrow & ")"

'format totals
Cells(lastrow + 1, 2).Select
Selection.AutoFill Destination:=Range("B156:K156"), Type:=xlFillDefault ' I think the problem is here. I need to have a
variable that will change according to number of rows

Rows("156:156").Select
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Range("B3:O157").Select
Selection.Replace What:="", Replacement:="0.00", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("B:B,D:D,F:F,H:H,J:J,L:L,N:N").Select
Selection.NumberFormat = "0"
Range("C:C,E:E,G:G,I:I,K:K,M:M,O:O").Select
Selection.NumberFormat = "#,##0.00"
 
N

Nigel

I guess two points

1. If you run the macro twice it will put the total in twice, since the code
to find the last row will be moved on to the total created in the first
run - not a good idea!

2. Your fill is OK just change the reference to the lastrow variable, so in
the part where you specify the range use

Range("B" & lastrow & ":K" & lastrow)

or use to allow control of columns using a loop or variable (if required)

Range(Cells(ir, 2), Cells(ir, 11))

Cheers
Nigel

poppy > said:
Thanx Nigel

I works fine except for one small detail. When I run the macro for a
second time it gives me problems. I think this is becuase I do a fill
function and I have hardcoded the destination fill. Does that make
sense to you?


Code:
--------------------
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Cells(lastrow + 1, 1) = "TOTAL"
Cells(lastrow + 1, 2).Formula = "=sum(B3:B" & lastrow & ")"

'format totals
Cells(lastrow + 1, 2).Select
Selection.AutoFill Destination:=Range("B156:K156"), Type:=xlFillDefault
Click to expand...
' I think the problem is here. I need to have a
 
Top