Write to First Visible Cell after autoFilter in VBA

[vikas_newports]

Hello

Have a great day, I am very new to Macros and want to create a dynamic macro and automate my day to day task .
I wrote code to copy data from other workbooks and append it on other workbook(testmac)

I applied a filter and select the All amount which are greater than Zero in Column K
Now I want to write " 0 " in all Filtered Cells (visible Cells) in Column K

I am stuck and unable to do this , looking for a better from your end.

I need more help in this code that how can I get rid of the manual file path, I want it as just run it from any folder without changing the path in code.

Here is my code

Sub testing()
'Open File1 copy data to testmac and close File1
    Workbooks.Open "C:\Users\Mark\Desktop\Automation\File1.csv"
    Workbooks("File1.csv").Worksheets("File1").Range("A5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy Workbooks("Testmac.xlsm").Worksheets("DATA").Range("A15")
    Workbooks("File1.csv").Close SaveChanges:=False
'Open File2 copy data to testmac and close File2
    Workbooks.Open "C:\Users\Mark\Desktop\Automation\File2.csv"
    Workbooks("File2.csv").Worksheets("File2").Range("A5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy Workbooks("Testmac.xlsm").Worksheets("DATA").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Workbooks("File2.csv").Close SaveChanges:=False
'Filter for Amounts greater than Zero
    Range("A14:P1048576").AutoFilter Field:=11, Criteria1:=">0"
    
End Sub

Please note I posted same question on excelforum but out of luck

 

[Domenic]

The following code will filter Column K for values greater than 0, then it enters 0 in the visible cells, and then clears the filter.

    Dim last_row As Long
    last_row = Cells(Rows.Count, "K").End(xlUp).Row

    With Range("A14:P" & last_row)
        .AutoFilter Field:=11, Criteria1:=">0"
        On Error Resume Next
        .Resize(.Rows.Count - 1, 1).Offset(1, 10).SpecialCells(xlCellTypeVisible).Value = 0
        On Error GoTo 0
        .AutoFilter 'clear the filter
    End With

Hope this helps!

 

[vikas_newports]

Thanks Domenic , its working flawlessly
As a beginner I am thankful for this idea

The following code will filter Column K for values greater than 0, then it enters 0 in the visible cells, and then clears the filter.

    Dim last_row As Long
    last_row = Cells(Rows.Count, "K").End(xlUp).Row

    With Range("A14:P" & last_row)
        .AutoFilter Field:=11, Criteria1:=">0"
        On Error Resume Next
        .Resize(.Rows.Count - 1, 1).Offset(1, 10).SpecialCells(xlCellTypeVisible).Value = 0
        On Error GoTo 0
        .AutoFilter 'clear the filter
    End With

Hope this helps!

 

[Domenic]

That's great, glad I could help.

Cheers!