Write to First Visible Cell after autoFilter in VBA

vikas_newports

Board Regular
Joined
May 26, 2016
Messages
94
Office Version
  1. 365
  2. 2010
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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!
 

vikas_newports

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

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

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
Top