Hi, Debra:

I often append data to the end of an older column of data, and want to leave
the old blank cells blank. How do I modify your macro in
http://www.contextures.com/xlDataEntry02.html to limit the fill-in to a
range, (in this case, the new data) rather than the entire column?

Thank you,

- Victoria

I'm not Debra, but ...

Select the range to fix and try this macro. (Remember to save first--just in
case it doesn't work the way you want. Then you can close without saving and
get your workbook back the way it was.)

Option Explicit
Sub FillColBlanks2()

Dim rng As Range

Set rng = Nothing
On Error Resume Next
Set rng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeBlanks))
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With Selection
.Value = .Value
End With

End Sub




VAMS wrote:

Hi, Debra:

I often append data to the end of an older column of data, and want to leave
the old blank cells blank. How do I modify your macro in
http://www.contextures.com/xlDataEntry02.html to limit the fill-in to a
range, (in this case, the new data) rather than the entire column?

Thank you,

- Victoria

--

Dave Peterson

Thank you!

"Dave Peterson" wrote:

I'm not Debra, but ...

Select the range to fix and try this macro. (Remember to save first--just in
case it doesn't work the way you want. Then you can close without saving and
get your workbook back the way it was.)

Option Explicit
Sub FillColBlanks2()

Dim rng As Range

Set rng = Nothing
On Error Resume Next
Set rng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeBlanks))
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With Selection
.Value = .Value
End With

End Sub




VAMS wrote:

Hi, Debra:

I often append data to the end of an older column of data, and want to leave
the old blank cells blank. How do I modify your macro in
http://www.contextures.com/xlDataEntry02.html to limit the fill-in to a
range, (in this case, the new data) rather than the entire column?

Thank you,

- Victoria

--

Dave Peterson