cell. find to locate bottome of a list (dynamically)

[RobShaw]

as per http://www.mrexcel.com/board2/viewtopic.php? p=608793#608793



thread I have a list that finds the next empty row and copys some fetched data on the bottom.

My next major challenge is as follows, I need to add a summation row at the bottom of the table, currently I do this by haveing 30-some rows and manipulate an autofilter to hide blanks.

I can use the method discussed in the topic above to find the last row and then copy in the sum formulae. The only thing is I don't know who to specify the range to sum dynamically.

so if you check the example in the previous post, the cell.find function sets target to the first empty cell in column A, I could run it again and find the next row, and I need to paste =sum(...) commands into the appropriate cells and dynamically set the range to the length of the list. The top row is always in the same place.

Any helps is apreciated.
Thanks[/url]

 

[NdNoviceHlp]

I didn't check your link but how about something like this? Dave

Private Sub CommandButton1_Click()
'dynamic sum of A1 to bottom
Dim rng As Range, answer As Long
Set rng = Range("A1:A" & Range("A1").End(xlDown).Row)
answer = Application.WorksheetFunction.Sum(rng)
Range("A" & Range("A1").End(xlDown).Row + 1) = answer
End Sub

 

[RobShaw]

I had to modify the code a little but only to start at different places on my sheet, and combine it with a cell.find command to make it dynamic properly, but the code worked perfectly.

Thanks

In case it helps any one else the whole macro looks like the following paste, bit of a moot point but it there a simple way of removing the repeated sections at the bottom, As you can see I have simply pasted the same block of code 5 times and changed the cells manually. It doesn't really matter, it would jsut be nice. I guess it hails back to college learning turbo C and neat code was a must.

Thanks again


code begins...

Sub Add_New_Row_quote()

'Cell.find to find empty row in column A
Range("A5").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True).Activate
target = ActiveCell.Address

'copy lookup row, need to remove formatting
Range("A3:H3").Select
Selection.Copy
'Paste stuff into 'target' row
'Copy contents of validated cells into listing vlaues only
Range(target).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Cancel copy selection
Application.CutCopyMode = False
Range("A6").Select

'Find first empty cell and paste sum formula there - ensure A# remains empty for next maro call
'Variables
Dim rng As Range, answer As Long

'Column C sum
Range("C6").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True).Activate
target = ActiveCell.Address
'dynamic sum of C6 to bottom
Set rng = Range("C6:C" & Range("C6").End(xlDown).Row)
answer = Application.WorksheetFunction.Sum(rng)
Range("C" & Range("C6").End(xlDown).Row + 1) = answer


'Column E sum
Range("E6").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True).Activate
target = ActiveCell.Address
'dynamic sum of E6 to bottom
Set rng = Range("E6:E" & Range("E6").End(xlDown).Row)
answer = Application.WorksheetFunction.Sum(rng)
Range("E" & Range("E6").End(xlDown).Row + 1) = answer

'Column F sum
Range("F6").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True).Activate
target = ActiveCell.Address
'dynamic sum of F6 to bottom
Set rng = Range("F6:F" & Range("F6").End(xlDown).Row)
answer = Application.WorksheetFunction.Sum(rng)
Range("F" & Range("F6").End(xlDown).Row + 1) = answer

'Column G sum
Range("G6").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True).Activate
target = ActiveCell.Address
'dynamic sum of G6 to bottom
Set rng = Range("G6:G" & Range("G6").End(xlDown).Row)
answer = Application.WorksheetFunction.Sum(rng)
Range("G" & Range("G6").End(xlDown).Row + 1) = answer

'Column H sum
Range("H6").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
True).Activate
target = ActiveCell.Address
'dynamic sum of H6 to bottom
Set rng = Range("H6:H" & Range("H6").End(xlDown).Row)
answer = Application.WorksheetFunction.Sum(rng)
Range("H" & Range("H6").End(xlDown).Row + 1) = answer

End Sub