Perform AutoFill or FillDown on a filtered column. Excel VBA

Question

Perform AutoFill or FillDown on a filtered column. Excel VBA

Navigation

#1 by (0 votes)

0

Good morning,

The problem I have is the following. After making a filter with criteria other than N / A, I am placed in the first filtered cell to perform a VLOOKUP. This operation is performed perfectly, the problem is as soon as I want to do an Autofill or Filldown which only makes a change from the first cell to N / A in the form of a general text.

I attach the code for your review

    ActiveSheet.Range("$A$1:$K$1113").AutoFilter Field:=10, Criteria1:="<>#N/A" _
    , Operator:=xlAnd

ActiveSheet.AutoFilter.Range.Offset(1).Columns(9).SpecialCells(xlCellTypeVisible)(1).Select

 ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC10,[Contratos.xls]Feuil1!R2C1:R738C2,2,0)"
    Application.Calculation = xlAutomatic
Selection.FillDown

Does anyone know what the problem may be? Thank you very much in advance.

vba excel

asked by Pablo Domenech 23.01.2017 в 14:32

source

1 answer

How to select a default value in Angular JS 1.5 in the combo Back to back button

score 0 · Accepted Answer

Good afternoon,

What I would do would be to copy the cell that contains the formula, and then select all the visible cells and paste the pastespecial xlFormulas so that it will paste the formula in all the cells visible after the filter.

Sub Testfiltro()

    Dim wb As Workbook, ws As Worksheet, LastRow As Long, Col As Long

    Set wb = ThisWorkbook 'Aquí le decimos a Excel que wb significa este libro
    Set ws = wb.Sheets("NombreHoja") 'Aquí cambia "NombreHoja" por el nombre de la hoja donde harás el filtro

    LastRow = ws.Range("A1").End(xlDown).Row ' Aquí miras cual es la última fila, así cuando la tabla aumente de tamaño filtra todo
    Col = ws.Range("A1").End(xlToRight).Column 'Lo mismo que antes, pero con columnas

    'LastRow y Col funcionan siempre y cuando no haya líneas ni columnas vacías entre la inicial y la final


    ' Podrías cambiar el Field:=10 por otra cosa si fuera variable o no estuviera siempre en el mismo sitio

    ws.Range(ws.Cells(1, 1), ws.Cells(LastRow, Col)).AutoFilter Field:=10, Criteria1:="<>#N/A" _
        , Operator:=xlAnd


    ws.AutoFilter.Range.Offset(1).Columns(9).SpecialCells(xlCellTypeVisible)(1).FormulaR1C1 = _
        "=VLOOKUP(RC10,[Contratos.xls]Feuil1!R2C1:R738C2,2,0)"
        Application.Calculation = xlAutomatic
    'Aquí copias la celda en la que acabas de hacer la fórmula
    ws.AutoFilter.Range.Offset(1).Columns(9).SpecialCells(xlCellTypeVisible)(1).Copy
    'Asumo que todo esto es en tu columna 10 y seleccionamos todas las celdas visibles en esa columna y pegamos como fórmulas
    ws.Range(ws.Cells(2, 10), ws.Cells(LastRow, 10)).SpecialCells(xlCellTypeVisible).PasteSpecial xlPasteFormulas

End Sub

I hope it serves you!