Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum - to end
Since I don't always know how many rows I will have in a spreadsheet, I
would like a macro that if I run the macros on a specific column, it automatically takes me to the end the filled column and drop down one more row to a blank cell and adds the entire column. My macro is cell specific using recorder and I don't want that. Thanks much for assistance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum - to end
sub sumtoend()
ac=activecell.column lr=cells(rows.count,ac).end(xlup).row cells(lr+2,ac)=application.sum(range(cells(1,ac),c ells(lr,ac)) end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Annette" wrote in message ... Since I don't always know how many rows I will have in a spreadsheet, I would like a macro that if I run the macros on a specific column, it automatically takes me to the end the filled column and drop down one more row to a blank cell and adds the entire column. My macro is cell specific using recorder and I don't want that. Thanks much for assistance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum - to end
Hi,
These couple of lines put a sum formula in the first cell below the data in column A Dim LastRow As Long LastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row Cells(LastRow + 1, 1).Formula = "=sum(A1:A" & LastRow & ")" -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Annette" wrote: Since I don't always know how many rows I will have in a spreadsheet, I would like a macro that if I run the macros on a specific column, it automatically takes me to the end the filled column and drop down one more row to a blank cell and adds the entire column. My macro is cell specific using recorder and I don't want that. Thanks much for assistance! . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum - to end
Give this a try. Click on the column letter and run.
Sub SumAColumn() Dim i As Long Dim j As Long Dim Drng As Range Set Drng = Selection j = Application.WorksheetFunction.CountA(Drng) i = Application.WorksheetFunction.Sum(Drng) Drng.End(xlDown).Offset(j, 0).Value = i End Sub HTH Regards, Howard "Annette" wrote in message ... Since I don't always know how many rows I will have in a spreadsheet, I would like a macro that if I run the macros on a specific column, it automatically takes me to the end the filled column and drop down one more row to a blank cell and adds the entire column. My macro is cell specific using recorder and I don't want that. Thanks much for assistance! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum - to end
After further review, I belief this will only work well if there is at least
one blank cell above the column of numbers to be summed. Otherwise the total is offset below the last value in the column by the number of values in the column. Regards, Howard "L. Howard Kittle" wrote in message ... Give this a try. Click on the column letter and run. Sub SumAColumn() Dim i As Long Dim j As Long Dim Drng As Range Set Drng = Selection j = Application.WorksheetFunction.CountA(Drng) i = Application.WorksheetFunction.Sum(Drng) Drng.End(xlDown).Offset(j, 0).Value = i End Sub HTH Regards, Howard "Annette" wrote in message ... Since I don't always know how many rows I will have in a spreadsheet, I would like a macro that if I run the macros on a specific column, it automatically takes me to the end the filled column and drop down one more row to a blank cell and adds the entire column. My macro is cell specific using recorder and I don't want that. Thanks much for assistance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|