How Do I Copy Value in Excel Cell Down to Blank Cells?

Questions & Suggestions
1

I want to fill down dates in excel column with new date.

Would this logic work and how would I express this in KM? Help appreciated.

Repeat
Copy cell value to Clipboard 1.
Move down 1 cell.
Copy next cell value to Default Clipboard.
If Default Clipboard blank then paste from Clipboard 1
else move value in Default Clipboard to Clipboard 1 and clear Default Clipboard..
Move down 1 cell.

2

Hi,

Can you clarify your goals?

Are you looking for:

15/08/15
16/08/15
17/08/15, etc?

3

Sorry

cell with a date value is copied and filled down into subsequent blank cells.
At each new date value same process, copy and fill down till next new date value.

Hope this is clearer.

Thanks

Michael

4

Yes, something like that process should work.

Terminology: use “System Clipboard” when you want to refer to just the normal system clipboard rather than a Named Clipboard.

“Default Clipboard” is just a badly named Named Clipboard and there is no need for it in this process.

Also note that you can only Copy or Paste from the System Clipboard, so everything has to go through that. So Copy to Named Clipboard "Whatever" really means Copy (to System Clipboard); and then assign the System Clipboard to Named Clipboard "Whatever", similarly Paste from Named Clipboard "Whatever" really means "Set the System Clipboard to Named Clipboard “Whatever”; Paste`.

And other final thing - spread sheets sometimes copy weird things when you copy cells. So you may need to double check that you get the right text. You may also want to remove the systems from the clipboards and verify that they contain only text.

1 Like
5

Hey Michael,

Starting with your first date selected.

⌘⇧↓

⇧↑

⌃D (Fill Down)

⌘↓

Repeat.

The best way to do this would be Visual Basic, but I can’t help with that.

-Chris

6

Another option would be to copy the entire column, and then iterate through the lines (For Each action, Lines In System Clipboard collection) and build a duplicate of the column in a variable, and then put the whole lot back in the clipboard and then Paste.

7

That's not a bad idea, and it can be easily done with AppleScript.

------------------------------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2017/03/29 01:06
# dMod: 2017/03/29 01:06 
# Appl: Microsoft Excel
# Task: Fill Down in a Single Column – Fill any blank cells following a non-blank cell.
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @Microsoft_Excel, @Excel, @Fill, @Down
------------------------------------------------------------------------------

tell application "Microsoft Excel"
   set eSel to value of selection
end tell

repeat with theSubList in eSel
   if contents of theSubList ≠ {""} then
      set dateItem to contents of theSubList
   else if contents of theSubList = {""} then
      set (contents of theSubList) to dateItem
   end if
end repeat

tell application "Microsoft Excel"
   set value of selection to eSel
end tell

------------------------------------------------------------------------------

-Chris

3 Likes
8

When your workflow is entirely within a MS Office app, it is often easiest and best to use a VBA macro. Here is a simple Excel VBA macro that should do the job.

Before you run the macro, select the entire range to be filled.
For example:

##example Results

###Excel VBA Macro

Sub Fill_Blanks_with_last_Value()
'
' Fill_Blanks_with_last_Value Macro
'
    Dim oRange As Range
    Dim oCell As Range
    Dim CellToCopy As Variant
    
    Set oRange = Selection
    CellToCopy = Null
    
    For Each oCell In oRange.Cells
    
    If (IsEmpty(oCell.Value)) Then
        oCell.Value = CellToCopy
    Else
        CellToCopy = oCell.Value
    End If
    
    Next
    
End Sub
1 Like
9

Thanks guys.

Michael the VBA works great but I have 2831 rows to fill and it stops at 18/0/15 - how do I get it to continue to the end?

Thanks

Michael

10

Did you select ALL cells down to the last row you want filled?

The only reason the VBA macro will stop is if it reaches the end of the selection.

However, I just noticed one thing: "18/0/15" does NOT look like a valid date to me. Are these supposed to be dates, or just text values?

11

Hi Michael

just could not get the vba script to run. Not sure why as all conditions seemed fulfilled.

But Chris, applescript processed an about 5 seconds - voila!

Thanks guys.

Michael

12

I'm confused by this statement. Just above you stated:[quote="michael_gilbert, post:9, topic:6678"]
Michael the VBA works great but I have 2831 rows to fill and it stops at 18/0/15 - how do I get it to continue to the end?
[/quote]

So it was running, correct?
What about the date issue ("18/0/15" is not a valid date)?

What versions of Excel and macOS are you running?

13

The VBA would run down to about 19/10/15 then stop. Copied complete column into new sheets then new workbook double checking that all cells had same format by copying and pasting format from first cell. Still no joy. Running El Capitan and Excel 2011 with latest update.

Tried with applescript and filled down in a few seconds. Could not find any anomaly in cell formatting or in values.

Sorted but unsure why the VBA did not complete column fill as checked and double checked formats as figured this must be the issue.

Cheers

Michael

14

The VBA loop is based on the selection you made in Excel.
Are you sure you selected all cells to be processed before running the VBA macro? Did the macro just stop, or did you get some kind of error or popup?

15

You don’t need a macro or KM for that. I wrote a blog post a little while back explaining how do to do this - https://krkessler.com/2017/02/20/fill-a-range-that-has-missing-values-in-excel/

1 Like
16

Kurt, thanks for sharing. That is a very clever solution.

17

Wow Kurt

yet another solution. Excel certainly does have some well hidden secrets.

Thanks

Michael