Solved: Advance Filter & Last Rows Sum

[ayazgreat]

Hi

i am using following macro for advance filter and I want Sum for last Rows from B to G with ColorIndex = 1 and Font.ColorIndex = 2 , Font.Bold= true and written "Total" for last row of A with ColorIndex = 1 and Font.ColorIndex = 2 , Font.Bold= true .




Sub Filter()
Range("Database").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _"Criteria"), CopyToRange:=Sheets(" Report").Range("A5:G5"), Unique:=FalseEnd Sub



Could any one please help me

Thanks in advance

[Simon Lloyd]

Would you like to upload a sample workbook so we can see what you are trying to achieve? summing each of the last rows is easy we need to see your destination for the copy and the sum we can then work out your formatting for you!

[ayazgreat]

Please See my attchaed sample workbook.

[Simon Lloyd]

The code below will add the formula needed to your columns and calculate, it's not a pretty solution but it works, you should be able to add your formatting to this code too!
Sub add_formula()
Dim cRow As Long
Dim Rng As Range
Dim cColumn As String
cRow = Range("B" & Rows.Count).End(xlUp).Row
Set Rng = Range("B6:B" & Range("B" & Rows.Count).End(xlUp).Row)
Range("B" & cRow + 1).Value = "=SUBTOTAL(9," & Rng.Address & ")"
MsgBox Range(Cells(cRow + 1, 2), Cells(cRow + 1, Columns.Count).End(xlToLeft)).Address
cColumn = Cells(Columns.Count).End(xlToLeft).Address
Range("B" & cRow + 1 & ":" & cColumn).Select
Selection.FillRight
Calculate
Range("A1").Select
End Sub

[ayazgreat]

Thanks Sir for your reply but your given codes are not working properly meaning to say that the result i wish to have is wrong please if possible check it with my attached sheet.

Thanks in advance

[Simon Lloyd]

This does what you want:

Sub add_formula()
Dim cRow As Long
Dim Rng As Range
Dim i As Integer
cRow = Range("B" & Rows.Count).End(xlUp).Row
Set Rng = Range(Cells(6, 2), Cells(cRow, 2))
Range("B" & cRow + 1).Value = "=SUBTOTAL(9," & Rng.Address & ")"
For i = 1 To 5
Range("B" & cRow + 1).Offset(0, i).Value = "=SUBTOTAL(9," & _
Range(Cells(6, i + 2), Cells(cRow, i + 2)).Address & ")"
Next i
Calculate
End Sub

[ayazgreat]

Thank you very much Sir this does what I want

[Simon Lloyd]

ayazgreat if your problem has been solved please mark your thread solved by using Thread Tools at the top of this window and use Mark Solved.

[Simon Lloyd]

When cross posting you should display the thread in both forums as its unfair to have two sets of people working on the same solution for you!
Cross Posted (http://www.mrexcel.com/forum/showthread.php?t=321393)

[ayazgreat]

When cross posting you should display the thread in both forums as its unfair to have two sets of people working on the same solution for you!


Yes Sir you are right next time I will be careful but thanks