Double-click autofill - dynamic based on adjacent

问题:

I need to do:

I am using the normal auto-fill function in Excel (double click the dot on the side of a cell) to copy the contents to the sub cells, so in this case clicking the dot in Cell A1 will do this:

I need a script that will repeat the process down the entire column, until there are no more values in the adjacent cell.

回答1:

Presumably this is what you're looking for:

Option Explicit
Sub FillInTheBlanks()
    Dim StartCell As Range, EndCell As Range
    Set StartCell = ActiveCell
    Set EndCell = ActiveSheet.Cells(ActiveSheet.UsedRange.Rows.Count + 1, StartCell.Offset(0, 1).Column).End(xlUp)

    Dim currentText As String
    Dim i As Long
    For i = StartCell.Row To EndCell.Row
        If Not IsEmpty(ActiveSheet.Cells(i, StartCell.Row)) Then
            currentText = ActiveSheet.Cells(i, StartCell.Row).Text
        Else
            ActiveSheet.Cells(i, StartCell.Row).Value = currentText
        End If
    Next i
End Sub

That code with perform the following:

---

If you really want what's in your screenshot, then you'll need to do this:

Option Explicit
Sub FillInTheBlanks()
    Dim StartCell As Range, EndCell As Range, NextCell As Range
    Set StartCell = ActiveCell
    Set EndCell = Cells(ActiveSheet.UsedRange.Rows.Count + 1, StartCell.Offset(0, 1).Column).End(xlUp)

    While StartCell.Row < EndCell.Row
        Set NextCell = StartCell.Offset(1, 1).End(xlDown).Offset(0, -1)
        StartCell.AutoFill Destination:=ActiveSheet.Range(StartCell, NextCell), Type:=xlFillDefault
        Set StartCell = NextCell.Offset(1, 0)
    Wend
End Sub

Which does this:

回答2:

You can do this from the keyboard...

• Select Column A
• Choose Home | Find & Select | Goto Special... Blanks OK
• Press [=] [up arrow] [ctrl+Enter]

To remove formulas, select Column A, copy and paste-special values.