Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last empty row
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last empty row
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 "poppy " wrote in message ... 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 skip 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 Regards --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last empty row
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 "poppy " wrote in message ... 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 skip 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 Regards --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last empty row
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. "Chris.F" wrote: 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 "poppy " wrote in message ... 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 skip 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 Regards --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last empty row
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" ------------------- Thanx agai -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last empty row
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 " wrote in message ... 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 ' 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" -------------------- Thanx again --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To find row is empty or not | Excel Discussion (Misc queries) | |||
Find first empty row | Excel Discussion (Misc queries) | |||
How to find next value if rows between are empty? | Excel Discussion (Misc queries) | |||
find the next empty row | Excel Discussion (Misc queries) | |||
Find Next Empty Row | Excel Programming |