PDA

View Full Version : Solved: Copy values to Empty cells below filled cells in selection



justdream
12-30-2010, 02:29 PM
Dears,

I'd like to write Macro to Copy values to Empty cells below filled cells in selection
For example: if pic.1 is the input data, I'd like Macro to give me output as in pic.2

Sean.DiSanti
12-30-2010, 05:17 PM
Public Sub blah()
thecurrent = ""
For x = 2 To ActiveSheet.UsedRange.Rows.Count
If ActiveSheet.Range("a" & x).Formula <> "" Then
thecurrent = ActiveSheet.Range("a" & x).Formula
Else
ActiveSheet.Range("a" & x).Formula = thecurrent
End If
Next x
End Sub

***edit*** forgot to increment variable

***edit 2.0*** i'm retarded today. forgot that i'd used a for not a while... incrementing obviously not necessary

shrivallabha
12-30-2010, 09:47 PM
You can use plain Excel (without macros)
1. Set Data >> Filter
2. Set the column "Action" Filter to "Blanks" while "Name" to "NonBlanks"
3. Then in the topmost cell considering it is A3, insert formula like

=A2
4. Copy the formula to the last Blank Cell. Excel will adjust the penultimate cell address on its own.

justdream
12-31-2010, 02:40 AM
Thanks Sean, that's exactly what I'm looking for..

Sean.DiSanti
01-03-2011, 04:33 PM
no problem, glad to help