Filter and Count Formula

[pwill]

Hi can anyone help with the following code for filtering Data and with Formulas for Counting Data?

I have Headers in Columns "A2:J2" and Data in "A3:J" Sheet1

I have the following macro that deletes all visible rows when filtering the data in columns "D:J" but I don't want to delete the 'Entire Rows' I only want to delete Data in columns "A3:J" and use 'delete ShiftUp' if possible?

[FONT=Verdana]Sub Filter()

[/FONT]
[FONT=Verdana]Dim Data As Worksheet: Set Data = Sheet1
    
    Application.ScreenUpdating = False
    
        With Data
            .Range(.Range("A3:J3"), .Range("A3:J3").End(xlDown)).Delete Shift:=xlUp
            .AutoFilterMode = False
        End With
    
End Sub
[/FONT]

Also, I have the following Formula that Counts Data from column "D"
Does anyone know how I can use this formula to count only the visible Data when filtering columns "D:J"

Formula '[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(COUNTIF(D:J,$L3)=0,"",COUNTIF(D:J,$L3))'[/FONT]


Regards

pwill

 

[DanteAmor]

If the data is filtered you can not only delete a part and scroll the data up, you would have to remove the filter, read record by record and if it meets the filter criteria then delete that data and scroll up.

To count if the data is filtered:

=SUMPRODUCT(SUBTOTAL(2,OFFSET($D$3:$J$3,ROW($D$3:$J$100)-3,))*($D$3:$J$100=$L3))

If you need help with the macro or with the formula, You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[pwill]

Thanks

DanteAmor, I will have a look at that much appreciated

pwill
​