Filter data based on a column and calculate average dynamically for all the filtered rows

S

sangria11

New Member
Joined
Oct 27, 2016
Messages
3
I have a table where I need to find the elements present in different samples.
For every sample, the no of iterations is a variable - I can have two rows of sample 1 and 3 rows of sample2 or 5 rows of sample4. the number of columns which are the elements can also be different. I have considered 3 samples and 17 elements in this case.


I need to filter based on the sample. All entries corresponding to sample1 needs to be copied to the next spreadsheet and the average needs to be calculated for the entire row of sample 1 , then sample 2 and so on


I am a beginner in vba and hence the code I used is not able to do it for dynamic range of values.


Also, I can only calculate the average using macro recorder. I am not aware how to combine these two codes into one. I tried to search a lot on this topic




I have included the expected results and my codes as well.


Any help would be much appreciated!!! Thank you

Table: (Data)

[TABLE="width: 1152"]
<colgroup><col width="64" span="18" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]Sample[/TD]
[TD="class: xl64, width: 64"]El1[/TD]
[TD="class: xl64, width: 64"]El2[/TD]
[TD="class: xl64, width: 64"]El3[/TD]
[TD="class: xl64, width: 64"]El4[/TD]
[TD="class: xl64, width: 64"]El5[/TD]
[TD="class: xl64, width: 64"]El6[/TD]
[TD="class: xl64, width: 64"]El7[/TD]
[TD="class: xl64, width: 64"]El8[/TD]
[TD="class: xl64, width: 64"]El9[/TD]
[TD="class: xl64, width: 64"]El10[/TD]
[TD="class: xl64, width: 64"]El11[/TD]
[TD="class: xl64, width: 64"]El12[/TD]
[TD="class: xl64, width: 64"]El13[/TD]
[TD="class: xl64, width: 64"]El14[/TD]
[TD="class: xl64, width: 64"]El15[/TD]
[TD="class: xl64, width: 64"]El16[/TD]
[TD="class: xl64, width: 64"]El17[/TD]
[/TR]
[TR]
[TD]Sample1[/TD]
[TD="align: right"]393331[/TD]
[TD="align: right"]1504481[/TD]
[TD="align: right"]75778[/TD]
[TD="align: right"]4417[/TD]
[TD="align: right"]943[/TD]
[TD="align: right"]123913[/TD]
[TD="align: right"]1390[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sample1[/TD]
[TD="align: right"]412063[/TD]
[TD="align: right"]1438405[/TD]
[TD="align: right"]76769[/TD]
[TD="align: right"]4454[/TD]
[TD="align: right"]44147820[/TD]
[TD="align: right"]129880[/TD]
[TD="align: right"]1580[/TD]
[TD="align: right"]22333[/TD]
[TD="align: right"]3.09E+08[/TD]
[TD="align: right"]73549450[/TD]
[TD="align: right"]30502700[/TD]
[TD="align: right"]3906902[/TD]
[TD="align: right"]15488000[/TD]
[TD="align: right"]24554970[/TD]
[TD="align: right"]18558080[/TD]
[TD="align: right"]7456669[/TD]
[TD="align: right"]11010050[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]428819[/TD]
[TD="align: right"]1371168[/TD]
[TD="align: right"]67321[/TD]
[TD="align: right"]3947[/TD]
[TD="align: right"]43852940[/TD]
[TD="align: right"]121679[/TD]
[TD="align: right"]1373[/TD]
[TD="align: right"]22640[/TD]
[TD="align: right"]2.95E+08[/TD]
[TD="align: right"]69845060[/TD]
[TD="align: right"]27878150[/TD]
[TD="align: right"]4000339[/TD]
[TD="align: right"]14100450[/TD]
[TD="align: right"]22153440[/TD]
[TD="align: right"]17136190[/TD]
[TD="align: right"]6752473[/TD]
[TD="align: right"]9827803[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]424563[/TD]
[TD="align: right"]1439610[/TD]
[TD="align: right"]71472[/TD]
[TD="align: right"]4484[/TD]
[TD="align: right"]44790560[/TD]
[TD="align: right"]126363[/TD]
[TD="align: right"]1350[/TD]
[TD="align: right"]21676[/TD]
[TD="align: right"]3.01E+08[/TD]
[TD="align: right"]69680380[/TD]
[TD="align: right"]28027090[/TD]
[TD="align: right"]3559731[/TD]
[TD="align: right"]14267580[/TD]
[TD="align: right"]22478890[/TD]
[TD="align: right"]16818990[/TD]
[TD="align: right"]6909726[/TD]
[TD="align: right"]9788499[/TD]
[/TR]
[TR]
[TD]Sample3[/TD]
[TD="align: right"]442984[/TD]
[TD="align: right"]1418608[/TD]
[TD="align: right"]66124[/TD]
[TD="align: right"]4571[/TD]
[TD="align: right"]46818160[/TD]
[TD="align: right"]103528[/TD]
[TD="align: right"]940[/TD]
[TD="align: right"]45648[/TD]
[TD="align: right"]3.16E+08[/TD]
[TD="align: right"]75111780[/TD]
[TD="align: right"]26088990[/TD]
[TD="align: right"]3618853[/TD]
[TD="align: right"]14567820[/TD]
[TD="align: right"]23493420[/TD]
[TD="align: right"]17066320[/TD]
[TD="align: right"]7516863[/TD]
[TD="align: right"]11084680[/TD]
[/TR]
[TR]
[TD]Sample3[/TD]
[TD="align: right"]438080[/TD]
[TD="align: right"]1437939[/TD]
[TD="align: right"]69390[/TD]
[TD="align: right"]4801[/TD]
[TD="align: right"]44814690[/TD]
[TD="align: right"]104542[/TD]
[TD="align: right"]863[/TD]
[TD="align: right"]43491[/TD]
[TD="align: right"]3.2E+08[/TD]
[TD="align: right"]75668430[/TD]
[TD="align: right"]27034060[/TD]
[TD="align: right"]3533046[/TD]
[TD="align: right"]15121640[/TD]
[TD="align: right"]23136970[/TD]
[TD="align: right"]17178350[/TD]
[TD="align: right"]7412860[/TD]
[TD="align: right"]11065210[/TD]
[/TR]
[TR]
[TD]Sample3[/TD]
[TD="align: right"]436716[/TD]
[TD="align: right"]1381712[/TD]
[TD="align: right"]64563[/TD]
[TD="align: right"]4671[/TD]
[TD="align: right"]48130480[/TD]
[TD="align: right"]106974[/TD]
[TD="align: right"]1237[/TD]
[TD="align: right"]55891[/TD]
[TD="align: right"]3.59E+08[/TD]
[TD="align: right"]81091740[/TD]
[TD="align: right"]28725960[/TD]
[TD="align: right"]3533523[/TD]
[TD="align: right"]15826380[/TD]
[TD="align: right"]25076590[/TD]
[TD="align: right"]19268820[/TD]
[TD="align: right"]8463176[/TD]
[TD="align: right"]12399330[/TD]
[/TR]
</tbody>[/TABLE]




Expected Results :

[TABLE="width: 1172"]
<colgroup><col span="9"><col><col span="8"></colgroup><tbody>[TR]
[TD]Sample1[/TD]
[TD="align: right"]393331[/TD]
[TD="align: right"]1504481[/TD]
[TD="align: right"]75778[/TD]
[TD="align: right"]4417[/TD]
[TD="align: right"]943[/TD]
[TD="align: right"]123913[/TD]
[TD="align: right"]1390[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Sample1[/TD]
[TD="align: right"]412063[/TD]
[TD="align: right"]1438405[/TD]
[TD="align: right"]76769[/TD]
[TD="align: right"]4454[/TD]
[TD="align: right"]44147820[/TD]
[TD="align: right"]129880[/TD]
[TD="align: right"]1580[/TD]
[TD="align: right"]22333[/TD]
[TD="align: right"]309360000[/TD]
[TD="align: right"]73549450[/TD]
[TD="align: right"]30502700[/TD]
[TD="align: right"]3906902[/TD]
[TD="align: right"]15488000[/TD]
[TD="align: right"]24554970[/TD]
[TD="align: right"]18558080[/TD]
[TD="align: right"]7456669[/TD]
[TD="align: right"]11010050[/TD]
[/TR]
[TR]
[TD]Average[/TD]
[TD="align: right"]402697[/TD]
[TD="align: right"]1471443[/TD]
[TD="align: right"]76273.5[/TD]
[TD="align: right"]4435.5[/TD]
[TD="align: right"]22074382[/TD]
[TD="align: right"]126896.5[/TD]
[TD="align: right"]1485[/TD]
[TD="align: right"]11170[/TD]
[TD="align: right"]154680003.5[/TD]
[TD="align: right"]36774727[/TD]
[TD="align: right"]15251350[/TD]
[TD="align: right"]1953453[/TD]
[TD="align: right"]7744000[/TD]
[TD="align: right"]12277487[/TD]
[TD="align: right"]9279040[/TD]
[TD="align: right"]3728336[/TD]
[TD="align: right"]5505029[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]428819[/TD]
[TD="align: right"]1371168[/TD]
[TD="align: right"]67321[/TD]
[TD="align: right"]3947[/TD]
[TD="align: right"]43852940[/TD]
[TD="align: right"]121679[/TD]
[TD="align: right"]1373[/TD]
[TD="align: right"]22640[/TD]
[TD="align: right"]294530000[/TD]
[TD="align: right"]69845060[/TD]
[TD="align: right"]27878150[/TD]
[TD="align: right"]4000339[/TD]
[TD="align: right"]14100450[/TD]
[TD="align: right"]22153440[/TD]
[TD="align: right"]17136190[/TD]
[TD="align: right"]6752473[/TD]
[TD="align: right"]9827803[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]424563[/TD]
[TD="align: right"]1439610[/TD]
[TD="align: right"]71472[/TD]
[TD="align: right"]4484[/TD]
[TD="align: right"]44790560[/TD]
[TD="align: right"]126363[/TD]
[TD="align: right"]1350[/TD]
[TD="align: right"]21676[/TD]
[TD="align: right"]301350000[/TD]
[TD="align: right"]69680380[/TD]
[TD="align: right"]28027090[/TD]
[TD="align: right"]3559731[/TD]
[TD="align: right"]14267580[/TD]
[TD="align: right"]22478890[/TD]
[TD="align: right"]16818990[/TD]
[TD="align: right"]6909726[/TD]
[TD="align: right"]9788499[/TD]
[/TR]
[TR]
[TD]Average[/TD]
[TD="align: right"]426691[/TD]
[TD="align: right"]1405389[/TD]
[TD="align: right"]69396.5[/TD]
[TD="align: right"]4215.5[/TD]
[TD="align: right"]44321750[/TD]
[TD="align: right"]124021[/TD]
[TD="align: right"]1361.5[/TD]
[TD="align: right"]22158[/TD]
[TD="align: right"]297940000[/TD]
[TD="align: right"]69762720[/TD]
[TD="align: right"]27952620[/TD]
[TD="align: right"]3780035[/TD]
[TD="align: right"]14184015[/TD]
[TD="align: right"]22316165[/TD]
[TD="align: right"]16977590[/TD]
[TD="align: right"]6831100[/TD]
[TD="align: right"]9808151[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample3[/TD]
[TD="align: right"]442984[/TD]
[TD="align: right"]1418608[/TD]
[TD="align: right"]66124[/TD]
[TD="align: right"]4571[/TD]
[TD="align: right"]46818160[/TD]
[TD="align: right"]103528[/TD]
[TD="align: right"]940[/TD]
[TD="align: right"]45648[/TD]
[TD="align: right"]316340000[/TD]
[TD="align: right"]75111780[/TD]
[TD="align: right"]26088990[/TD]
[TD="align: right"]3618853[/TD]
[TD="align: right"]14567820[/TD]
[TD="align: right"]23493420[/TD]
[TD="align: right"]17066320[/TD]
[TD="align: right"]7516863[/TD]
[TD="align: right"]11084680[/TD]
[/TR]
[TR]
[TD]Sample3[/TD]
[TD="align: right"]438080[/TD]
[TD="align: right"]1437939[/TD]
[TD="align: right"]69390[/TD]
[TD="align: right"]4801[/TD]
[TD="align: right"]44814690[/TD]
[TD="align: right"]104542[/TD]
[TD="align: right"]863[/TD]
[TD="align: right"]43491[/TD]
[TD="align: right"]320310000[/TD]
[TD="align: right"]75668430[/TD]
[TD="align: right"]27034060[/TD]
[TD="align: right"]3533046[/TD]
[TD="align: right"]15121640[/TD]
[TD="align: right"]23136970[/TD]
[TD="align: right"]17178350[/TD]
[TD="align: right"]7412860[/TD]
[TD="align: right"]11065210[/TD]
[/TR]
[TR]
[TD]Sample3[/TD]
[TD="align: right"]436716[/TD]
[TD="align: right"]1381712[/TD]
[TD="align: right"]64563[/TD]
[TD="align: right"]4671[/TD]
[TD="align: right"]48130480[/TD]
[TD="align: right"]106974[/TD]
[TD="align: right"]1237[/TD]
[TD="align: right"]55891[/TD]
[TD="align: right"]359420000[/TD]
[TD="align: right"]81091740[/TD]
[TD="align: right"]28725960[/TD]
[TD="align: right"]3533523[/TD]
[TD="align: right"]15826380[/TD]
[TD="align: right"]25076590[/TD]
[TD="align: right"]19268820[/TD]
[TD="align: right"]8463176[/TD]
[TD="align: right"]12399330[/TD]
[/TR]
[TR]
[TD]Average[/TD]
[TD="align: right"]439260[/TD]
[TD="align: right"]1412753[/TD]
[TD="align: right"]66692.33[/TD]
[TD="align: right"]4681[/TD]
[TD="align: right"]46587777[/TD]
[TD="align: right"]105014.7[/TD]
[TD="align: right"]1013.333[/TD]
[TD="align: right"]48343.33[/TD]
[TD="align: right"]332023333.3[/TD]
[TD="align: right"]77290650[/TD]
[TD="align: right"]27283003[/TD]
[TD="align: right"]3561807[/TD]
[TD="align: right"]15171947[/TD]
[TD="align: right"]23902327[/TD]
[TD="align: right"]17837830[/TD]
[TD="align: right"]7797633[/TD]
[TD="align: right"]11516407[/TD]
[/TR]
</tbody>[/TABLE]


Code: 
Sub sorttable()




    Dim j As Long 'row variable


    On Error GoTo Err_Execute


    Dim i As Long


'Start search in row 1 in sheet1
    j = 1


'Column counter for sheet2
   i = 1


  
   
 While Len(Range("A" & CStr(j)).Value) > 0


        If Range("A" & CStr(j)).Value = "Sample1" Then


            Range(Range("A" & CStr(j)), Range("A" & CStr(j)).End(xlToRight)).Select
            Selection.Copy
            Sheets("Sheet2").Select
            Sheet2.Cells(i, 1).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    ' Debug.Print Cells(j + 1, 1) = "=AVERAGE(A1:C" & j - 1 & ")"   ' This is the line i used to calculate average in between the cells, I guess it is wrong and it gives me error
            'Move counter to next Column
            i = i + 1
            'Go back to Sheet1 to continue searching
            Sheets("Sheet1").Select


        ElseIf Range("A" & CStr(j)).Value = "Sample2" Then


            Range(Range("A" & CStr(j)), Range("A" & CStr(j)).End(xlToRight)).Select
            Selection.Copy
                   Sheets("Sheet2").Select
            Sheet2.Cells(i, 1).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            'Move counter to next Column
           i = i + 1
           Sheets("Sheet1").Select




        ElseIf Range("A" & CStr(j)).Value = "Sample3" Then


            Range(Range("A" & CStr(j)), Range("A" & CStr(j)).End(xlToRight)).Select
            Selection.Copy
                  Sheets("Sheet2").Select
            Sheet2.Cells(i, 1).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            'Move counter to next Column
            i = i + 1
            'Go back to Sheet1 to continue searching
            Sheets("Sheet1").Select




        End If


            j = j + 1


      Wend


     
   Application.CutCopyMode = False


   MsgBox "the values have been extracted"


   Exit Sub


Err_Execute:
   MsgBox "Error Occured"


End Sub


[\code]

[code]

Sub test()
'
' Test Macro
' Using macro recorder
'


'
    Rows("1:1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Rows("1:1").Select
    ActiveSheet.Paste
    Range("A3").Select
    Sheets("Sheet1").Select
    Range("2:2,3:3").Select
    Range("A3").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Rows("3:3").Select
    ActiveSheet.Paste
    Range("B5").Select
    Application.CutCopyMode = False
    Range("A5").Select
    Selection.Style = "Normal 2"
    ActiveCell.FormulaR1C1 = "Average"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "= AVERAGE(R[-2]C,R[-1]C)"
    Range("E7").Select
    ActiveWindow.SmallScroll Down:=2
    Range("B5").Select
    Selection.AutoFill Destination:=Range("B5:R5"), Type:=xlFillDefault
    Range("B5:R5").Select
    Range("A7").Select
    Sheets("Sheet1").Select
    Rows("4:5").Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Range("A5").Select
    Columns("A:A").EntireColumn.AutoFit
    Application.CutCopyMode = False
    Selection.Copy
    Range("A9").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("A9:B9"), Type:=xlFillDefault
    Range("A9:B9").Select
    Range("B9").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "= AVERAGE(R[-2]C,R[-1]C)"
    Range("B9").Select
    Selection.AutoFill Destination:=Range("B9:R9"), Type:=xlFillDefault
    Range("B9:R9").Select
    Range("A11").Select
    Sheets("Sheet2").Select
    Range("A27").Select
    Sheets("Sheet1").Select
    Range("A8:R10").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A11").Select
    ActiveSheet.Paste
    Range("A14").Select
    Application.CutCopyMode = False
    Selection.Style = "Normal 2"
    ActiveCell.FormulaR1C1 = "Average"
    Range("B14").Select
    ActiveCell.FormulaR1C1 = "= AVERAGE(R[-3]C:R[-1]C)"
    Range("B14").Select
    Selection.AutoFill Destination:=Range("B14:R14"), Type:=xlFillDefault
    Range("B14:R14").Select
    Range("A16").Select
End Sub


Sub copy_filtered_data()
' Worksheets("Results").Columns("A:Z").Delete Shift:=xlToLeft
On Error Resume Next
Worksheets("Sheet2").UsedRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Results").Range("A1")
End Sub




Option Explicit
 
Sub Filter_Copy()
     
    Dim nextrow As Long
    Dim c As Range, rngCriteria As Range
     
    Worksheets.Add().Name = "Filtered_Data"
     
    Application.ScreenUpdating = False
     
    With Sheet3
        .AutoFilterMode = False
        Set rngCriteria = .Range("A1:A5")
        For Each c In rngCriteria
            nextrow = Worksheets("Filtered_Data").Cells(Rows.Count, "A").End(xlUp).Row
            .Range("A1:B" & .Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter field:=1, Criteria1:="=" & c.Value
            .Range("A1:B" & .Cells(Rows.Count, "A").End(xlUp).Row).Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy _
            Worksheets("Filtered_Data").Range("A" & nextrow + 1)
        Next c
    End With
     
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
     
End Sub






Sub tester()
Dim lr As Long
Dim ws As Worksheet
Dim colno, i, j As Integer ' colno -- is the column on which the filter is present
Dim icol As Long
Dim arr As Variant
Dim header As String
Dim header_row As Integer
Dim rng As Variant


colno = 1
Set ws = Sheets("Sheet1")
lr = ws.Cells(ws.Rows.Count, colno).End(xlUp).Row
header = "A1:Q1"
header_row = ws.Range(header).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
'    For i = 2 To lr
'        If ws.Cells(i, colno) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, colno), ws.Columns(icol), 0) = 0 Then
'        ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, colno)
'        End If
'    Next
arr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
'    ws.Columns(icol).Clear
   For i = 2 To lr
      ws.Range(header).AutoFilter field:=1
           ' Range(Range("A" & CStr(i)), Range("A" & CStr(i)).End(xlToRight)).Select
            'ws.Range("A1:C7").AdvancedFilter Action = xlFilterCopy, CriteriaRange = ws.Range("A1"), CopyToRange = Sheets("Sheet2").Range("A1"), Unique = False
           Debug.Print AutoFilter.Range.Copy
            Sheets("Sheet2").Select
            Sheet2.Cells(1, i).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            'Move counter to next Column
            i = i + 1
            ws.Select
    Next
  
            j = j + 1


        Application.CutCopyMode = False
   
        MsgBox "All matching data has been copied."
        ws.AutoFilterMode = False
        ws.Activate
End Sub




[\code]
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Sub filtercopy , copy_Filtered data and tester are not part of the codes i am using. I used it to test and unfortunately that got pasted as well
 
Upvote 0
this seems to be like built-in functionality, add subtotals. select a cell in data range and then ALT-D-B
(and ALT-D-B then ALT-R to remove)

can you use that?
if you need on a different sheet, just copy data then do subtotals
hth
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
[VBA] - Need to Hide one column and need sum of columns
Replies
0
Views
182
rsolanki
R
M
  • Solved
VBA to Insert Mismatched Row in separate worksheet
2 3
Replies
21
Views
635
Peter_SSs
Peter_SSs
S
  • Question Question
Macro Formatting to all WS
Replies
4
Views
278
MARK858
MARK858
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
238
willow1985
willow1985
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
239
rlv01
R

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

Share this page

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
Back
Top