Short cuts for converting formulas to values

[Guest]

Dear Sir,

May I know how to convert formulas to values under Excel 2002 and Excel 2007 ?
Is Copy and Paste Special is the only way ? Any keyboard short cut or
single button for this ?

Is there any short cut way of filling up the blank cells with the reference
just above it ?
<Usually I copy and paste reference by reference>
Eg.

Before
AFD220
BLANK
BLANK
BLANK
AFD221
BLANK
BLANK
AFD222
BLANK
BLANK ….

After
AFD220
AFD220
AFD220
AFD220
AFD221
AFD221
AFD221
AFD222
AFD222
AFD222

Thanks

Low

 

[Guest]

customize your menu bar to add the icon for paste values. (Right click menu
bar, customize, commands, edit - and drag the paste values icon to your
toolbar)
Then just copy and click the icon.

Of course it's always easier if you can have all the ones you want in one
column.

 

[Don Guillett]

this will do either for column A. Just comment out the one you don't want to
use.

Sub fillinblanks()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For i = 1 To lr
If Cells(i + 1, "a") = "" Then Cells(i + 1, "a") = Cells(i, "a")
Cells(i, "a").Value = Cells(i, "a").Value
Next
End Sub

 

[Dave Peterson]

#1. You can select the range
rightclick and grab it by the edge (not the autofill button)
move it ever so slightly to the right (or left or top or bottom)
then move it back and release the mouse button

You'll see a popup. Choose Copy here as values only.

#2. See Debra Dalgleish's site for some tips:
http://contextures.com/xlDataEntry02.html

Personally, I find that doing it manually is quicker than running the macro!

 

[Guest]

Dear Dave,

I tried #1, it worked out well.

Thanks

Low

 

[Guest]

Dear Don,

Thanks for your info.

Low

 

[Guest]

Dear Widman,

Thanks for your help.

It works out very well.

Kind Regards

Low