Need Help Clearing Specific Data in multiple columns and rows Using a Macro

[MrClueless]

This is a portion of a Macro that I am creating. What this portion of the macro does is filters column D based on "Piece Quantity" and Clear Contents in Column D and Column H. Column H contains data that corresponds with Piece Quantity and needs to be removed as well. The Problem that I am running into is in when it selects a specific cell, in this case Range("D471"). The problem is that this macro will be used on a weekly basis and the data is always changing so D471 will not always be the first row in column D after the spreadsheet has been filtered. Is it possible to have the macro look at Column D and find the first row containing Piece Quantity. The rest of the macro is working fine.


Windows("PieceRateRPT_060913_800AM.xlsx").Activate
Selection.AutoFilter
ActiveSheet.Range("A:P").AutoFilter Field:=4, Criteria1:= _
"Piece Quantity"
Range("D471").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.AutoFilter

 

[apo]

Hi..
Add the following Function to a Module.. it finds the first visible row after Autofiltering..
I tested this on a test workbook on Sheet1.. change that to suit..

One thing.. i noticed that the cells from D:P are all getting cleared.. is that what you want?

Function FirstVisibleValue(ByRef Sht As Worksheet, ByVal FilterCol As Long)
    Dim R As Range
    If Sheets("Sheet1").AutoFilterMode Then
        Set R = Sheets("Sheet1").AutoFilter.Range
        FirstVisibleValue = R.Offset(1, FilterCol - 1).Resize(R.Rows.Count, 1).SpecialCells(12).Cells(1).Address
    End If
End Function

Dim FirstRowAfterFilter
'Windows("PieceRateRPT_060913_800AM.xlsx").Activate
'Selection.AutoFilter
ActiveSheet.Range("A:P").AutoFilter Field:=4, Criteria1:= _
"Piece Quantity"
FirstRowAfterFilter = FirstVisibleValue(ActiveSheet, 4)
Range(FirstRowAfterFilter).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.AutoFilter

 

[MrClueless]

I tried inputting the code that you provided, however I get an error code. Stating Compile Error Sub or Function not defined. This error is referring too "FirstVisibleValue". and to answer your question in reference to if I need D and P to be cleared. Yes, I need to remove the paycode piece quantity for it is benign. There are other Paycodes in column D and I need to remove Piece Quantity and its Values in Column P. Thank you for your quick response.

Hi..
Add the following Function to a Module.. it finds the first visible row after Autofiltering..
I tested this on a test workbook on Sheet1.. change that to suit..

One thing.. i noticed that the cells from D:P are all getting cleared.. is that what you want?

Function FirstVisibleValue(ByRef Sht As Worksheet, ByVal FilterCol As Long)
    Dim R As Range
    If Sheets("Sheet1").AutoFilterMode Then
        Set R = Sheets("Sheet1").AutoFilter.Range
        FirstVisibleValue = R.Offset(1, FilterCol - 1).Resize(R.Rows.Count, 1).SpecialCells(12).Cells(1).Address
    End If
End Function

Dim FirstRowAfterFilter
'Windows("PieceRateRPT_060913_800AM.xlsx").Activate
'Selection.AutoFilter
ActiveSheet.Range("A:P").AutoFilter Field:=4, Criteria1:= _
"Piece Quantity"
FirstRowAfterFilter = FirstVisibleValue(ActiveSheet, 4)
Range(FirstRowAfterFilter).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.AutoFilter

 

[apo]

Hi..

My guess would be that you are not adding the Function correctly..

The Functions sole purpose is to find the First visible value after Autofilter has been applied.

To add the Function.. do this..
1. Open up a your workbook.
2. Go to the VB Editor.. (Press [Alt] + F11)
3. Insert a new module (Insert -> Module)
4. Paste the function code

The Function is called by this line from the code in your Sheets code..

FirstRowAfterFilter = [B]FirstVisibleValue[/B](Activesheet,4)