Excel-auto copy

Excel-auto copy

Didn't find your answer?

Here is a question I knew the answere before but now I just can't remember how to do it now. In column A, I used "go to" to select all the blank cells in the range, then I wanted to make these selected blank cells to auto copy the contents of their upper row cells, and I was stuck here. I know there's a way to do it, and I did it before, I just can't remember how. Any tips in this regard is greatly appreciated.
Ray Yaukip

Replies (3)

Please login or register to join the discussion.

avatar
By ACDWebb
31st Mar 2004 14:39

Or the heavy handed macro way
Open the VBA window in Excel (Tools, Macro, Visual Basic Editor)

Create a VB module

Copy the following code into that module
'here
Sub FillBlanks()
Dim tmp
Dim aCell
Dim sRng As Range
Dim eRng As Range

On Error Resume Next
Set sRng = Application.InputBox(prompt:="Select cell to start check from", _
Title:="Start from", _
Type:=8)
On Error Resume Next
Set eRng = Application.InputBox(prompt:="Select cell to finish check at", _
Title:="End at", _
Type:=8)
If sRng Is Nothing Or eRng Is Nothing Then
MsgBox prompt:="You have failed to set either the" & vbCr & _
"cell to start from or finish at" & vbCr & vbCr & _
"The routine will not be run", _
Title:="!!ERROR!!"
GoTo Finish
End If
On Error GoTo 0
For Each aCell In Range(sRng.Address, eRng.Address)
If aCell = "" Then aCell.Value = aCell.Offset(-1, 0).Value
Next aCell
Finish:
Set eRng = Nothing
Set sRng = Nothing
End Sub
'to here

Run the macro you have just created called FillBlanks

Thanks (0)
Simon Hurst
By Simon Hurst
31st Mar 2004 09:36

Use paste-special?
Hi Ray

I'm sure there is probably a more elegant way, but if all the cells in the column originally contain values, once you have used Control-Enter as I suggested before, could you copy the whole column and then use Paste-Special, Values to paste it back over itself as values. Obviously any formulae in the column would be lost.

Hope this helps

Simon

Thanks (0)
Simon Hurst
By Simon Hurst
30th Mar 2004 09:01

Control-enter
If I understand correctly, you want to put the same formula in all the selected cells - i.e. a reference to the cell immediately above each selected cell. Try the following:

With the blank cells selected, enter the correct formula for the first blank cell, then instead of just pressing return, hold down the control key and press return. This should put the formula into all of the selected cells, and they should all refer to the cell one above. Check carefully to make sure it works as you intend.

I hope it works

Simon Hurst
www.tkb.co.uk

Thanks (0)