Autofill - How to select the second line

Question

Guys, I got the following problem:

I’m filling a cell with a formula and I need it to be duplicated for the others. So I’m using the code

Selection.AutoFill Destination:=Range("C2:C329")

Problem:

Whenever I run this function, I filter the column to display only the #N/D. and then run the code again (with other parameters).

But it will always run the code in this range C2:C329, and it may be that within this range I don’t have the #N/D.

What I need:

I always need to select the second line of the spreadsheet, because the first is static.

tried with the code

    Range("a1").Select
ActiveCell.Offset(1, 1).Activate

But to no avail.

Can you help me.

Answer 1

I’m not quite sure I understood your question, but if you want to leave the variable range according to your filter result, you can replace it:

Destination:=Range("C2:C329")

for:

Destination:=Range(Selection, Selection.End(xlDown))

Thus, all cells below the active reference will always be selected.

NOTE: I’m assuming you don’t have any blank lines in your selection, as it will be filtered.

edited

if you want to replace cells that have #N/D in a column you can use the following approach:

Note that I am using On Error Resume Nextwhich is not recommended, but it solves your problem by the hour. If you can think of something to circumvent the incompatibility when the code compares "" to an Error it would be a good improvement.

Sub replaceNAs()
  On Error Resume Next 'quando a macro encontrar #N/D o valor não é comparável com "",
  Dim offsetCount As Integer
  Dim ref As Range: Set ref = Range("C2") ' célula de referência
  offsetCount = 0 'valor do offset

  While Not ref.Offset(offsetCount, 0).Value = "" 'enquanto o valor da célula não for vazio
    If Application.WorksheetFunction.IsNA(ref.Offset(offsetCount, 0).Value) Then 'caso seja #N/D
      If ref.Offset(offsetCount, 0).Value = CVErr(xlErrNA) Then
        ref.Offset(offsetCount, 0).FormulaR1C1 = ref.FormulaR1C1 'a célula atual recebe a fórmula da célula de referencia
      End If
    End If
    offsetCount = offsetCount + 1
  Wend 
End Sub

Answer 2

Dim lastRow As Long lastRow = Cells(Cells.Rows.Count, "A"). End(xlUp). Row

I take my column that contains more data, in which case it’s column "A", there will never be a blank cell.

Selection.Autofill Destination:=Range(Selection, "B" & lastRow)

Then I perform Autofill from my current cell (selected), until the column where I will play the form, within the range B & lastRow lastRow, this being the number of lines, from the last cell of "A" that contains data, until A1.