AutoFill - How to select the second line

Question

AutoFill - How to select the second line

Navigation

#1 by (2 votes)
#2 by (1 votes)

3

Dear, I have the following problem:

I'm filling a cell with one formula and need to be duplicated for the other cells. So I'm using the code

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

Problem:

Whenever I run this function, I make a filter on the column to display only the # N / A. 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 that range I do not have the # N / A.

What I need:

I always have to select the second row of the worksheet, because the first one is static.

I tried the code

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

But without success.

You can help me.

macro excel vba excel-vba

asked by anonymous 09.10.2017 / 16:36

2 answers

2

I'm not sure I understood your question, but if you want to leave the range variable, depending on the result of your filter, you can substitute:

Destination:=Range("C2:C329")

by:

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

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

NOTE: I'm assuming you have no blank lines in your selection since it will be filtered.

edited

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

Notice that I'm using On Error Resume Next which is not recommended, but solves your problem by the hour. If you can think of something to circumvent the incompatibility when the code compares with 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

09.10.2017 / 18:20

What exactly does the Application.DoEvents () method do? How do I trigger an event when the value of a variable changes?

score 1 · Accepted Answer

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

I get my column that contains more data, in this case it's column "A", there will never be a blank cell.

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

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