Crazy Cells

[BobH]

I have this spreadsheet that is calculating differences between dates and current date. The source for the data came from a web page table where whomever created it came up with a crazy arrangement when dates were the same for a group of entries. They put the date in a single cell and stretched it to cover all the rows. Stated differently, the date appears in a single cell but applies to multiple rows. This makes sorting or examining the data difficult to impossible while preserving data integrity.

Example below:

Cell example.jpg

I don't even know how this could be done - inserting a single cell row in a column that applies to multiple rows; so I'm at a loss to know how to undo it.

What I need is for the date values to be populated into a cell in each row. I would try to do it manually, but I don't know how to insert the cells I'd need or to resize the individual cells. If it matters, the cell row coordinates where the multi-row dates occur are always the row number of the first occurrence.

I guess my questions are 2:
1) How are the cells stretched across multiple rows and what happened to the cells they appear over - the subsequent cells in that column for the rows not presented?
2) Are there functions or formulas that would help me fix this, or am I going to have to do it manually?

The list runs to hundreds of entries; so I would prefer to avoid the tedious and error prone process of doing it manually. Also, I'm not VBA literate and cannot write a routine (even if I knew what variables to test for) to automate the process.

[Rus]

In Excel 2010, to UnGroup the cells select the whole column and select 'Unmerge cells' from the 'Merge & Center' Menu.
From there you can write a macro to copy down the blank cells or if you don't need them delete rows with blank cells.

[HansV]

Web pages have their own methods to merge cells in tables, but in Excel it is done by selecting a bunch of cells and clicking "Merge and Center" on the Home tab of the ribbon:

S0727.png

Clicking the same button will unmerge the cells again. It acts as a toggle.

You can select the data, then run the following macro to unmerge the cells and fill the blanks:

Code: Select all

Sub UnmergeAndFill()
    Dim rng As Range
    Dim rngArea As Range
    Selection.MergeCells = False
    Set rng = Selection.SpecialCells(xlCellTypeBlanks)
    rng.FormulaR1C1 = "=R[-1]C"
    For Each rngArea In rng.Areas
        rngArea.Value = rngArea.Value
    Next rngArea
End Sub

[BobH]

Thank you both, Rus and Hans!

I was able to do the merge and center command after selecting the column. I now have empty cells that I need to populate with the value in the last non-empty cell above them.

Hans, can you guide me toward learning what I need to know to create and run that macro?

Thanks again!

[HansV]

First, select the range with the cells.
Press Alt+F11 to activate the Visual Basic Editor.
Select Insert | Module to create a new code module.
Switch to this thread in your browser.
Click 'Select All' above the code block, then press Ctrl+C to copy the code.
Switch back to the Visual Basic Editor.
Press Ctrl+V to paste the code.
Click anywhere in the code (somewhere between Sub ... and End Sub).
Press F5 or select Run | Run Sub/Userform to run the macro.
Switch back to Excel to view the result.

[Rudi]

Hans, can you guide me toward learning what I need to know to create and run that macro?

You can also browse this thread...How to integrate macros into Excel...

[Rudi]

I now have empty cells that I need to populate with the value in the last non-empty cell above them.

A non-macro way (and still VERY quick, to do this filling in of blanks...

1. Select the entire column (or range of cells) containing the intermittent blank cells
2. Press F5 (to open the Go To dialog)
3. Click the "Special" button
4. Select *blanks* from the Special Cells dialog
5. Click OK
6. Right click on ANY selected blank cell
7. From the context menu select Delete and the Entire Row.

[HansV]

Uh - how will deleting rows fill blank cells?

BTW, congrats on making post number 123456:

S0729.png

[Rudi]

Uh - how will deleting rows fill blank cells?

I am so brainwashed in using/sharing THIS particular Excel tip, that it sneaked in without me realizing what Bob wanted...sorry!

Here are the correct steps:

1. Select the entire column (or range of cells) containing the intermittent blank cells
2. Press F5 (to open the Go To dialog)
3. Click the "Special" button
4. Select *blanks* from the Special Cells dialog
5. Click OK
6. While the blank cells are selected, start to type and equal sign ( = )
7. Press the up arrow (which selects a cell above and creates a formula)
8. Press CTRL+ENTER to automatically fill the other blank cells with the same (relative) formula
9. Copy the column and paste as values

[Rudi]

BTW, congrats on making post number 123456:

TX
Do I get a choccie bar or something for that? (How about a KitKat?)

[BobH]

BTW, congrats on making post number 123456:

TX
Do I get a choccie bar or something for that? (How about a KitKat?)

You will have to speak with Viking Bob about the Kit Kat.