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

RobShaw

Board Regular
Joined
Dec 21, 2004
Messages
76
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]
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,921
I didn't check your link but how about something like this? Dave
Code:
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

Board Regular
Joined
Dec 21, 2004
Messages
76
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
 

Forum statistics

Threads
1,148,529
Messages
5,747,230
Members
424,070
Latest member
smanni3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top