Excel VBA autofilter then change field value and filldown with error handling for blank cell possibilities

Question

Filtered data for column M is not filling down visible with my input change to first visible row of data in column M. It is filling down for first row in the first cell beyond the header row for column M not my input into the first visible cell in M. It is not validating data is returned and filling down the first row down. Not skipping because there was no data to fill down.

I have tried many different pieces of code I have found on visible and selection and filling down to no avail.

Sub DataData ()
'Change Active Worksheet to Raw Data
    Worksheets("Raw Data").Activate

    Application.ScreenUpdating = False

    ActiveSheet.Range("$A$1:$Z$" & Rows.Count).End(xlUp).AutoFilter Field:=4, Criteria1:= _
        "*Data*"

    With Worksheets("Raw Data").AutoFilter.Range
       Range("M" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
    End With

    ActiveCell.FormulaR1C1 = " DataData "

    With Worksheets("Raw Data").AutoFilter.Range
       Range("M" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
    End With

    With ActiveSheet.UsedRange.Select
    se
    .Resize(.Rows.Count - 1).Offset(1).Columns("M"). _
       SpecialCells(xlCellTypeVisible).FillDown
     End With

    ActiveSheet.ShowAllData

I am trying to filter Data on the 4th column, then in row M on first visible cell for that column, input DataData (if there was anything found in the filter), then fill down only visible data from first cell visible in column M.

Can i request you split that first paragraph into bullets or something? I'm having trouble figuring out each of the issues as some of the sentences seem to have duplicate points. — Cyril, Apr 15 '19 at 17:02
Also, please see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Cyril, Apr 15 '19 at 17:04

score 0 · Answer 1 · answered Apr 15 '19 at 18:08

Going to try and fix what I think you're doing and wanting:

option explicit 'put this at the top of your module

Sub DataData()
    Dim lr as long
    With Sheets("Raw Data")
        lr = .cells(.rows.count,1).end(xlup).row
        .Range(.Cells(2,"M"),.Cells(lr,"M")).SpecialCells(xlCellTypeVisible).value = " DataData "
        .ShowAllData
    End With
End Sub

Excel VBA autofilter then change field value and filldown with error handling for blank cell possibilities

1 Answers1