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.
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
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
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