Write to First Visible Cell after autoFilter in VBA

vikas_newports

Board Regular
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
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

VBA 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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.

VBA Code:
    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!
 
Upvote 0
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.

VBA Code:
    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!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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