Microsoft Excel Column expansion with data values

Hi,
I have a excel over 90K rows.  In columns there are some blank cells , I would like to fill those blanks cells with their last value.
Please find attached sample excel. Input column and desired output column
Sample-Excel.xlsx
kumarasinghAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

With a formula

Regards
Copy-of-Sample-Excel.xlsx
0
Rgonzo1971Commented:
Hi

Or with Code

Sub Macro()

Set myRange = Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp))

For Each cell In myRange
    If cell.Value = "" Then
        cell.Value = cell.Offset(-1, 0).Value
        cell.NumberFormat = cell.Offset(-1, 0).NumberFormat
    End If
Next
End Sub

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
broro183Commented:
hi Kumara,

Here is another code approach which may be faster. However, it may need some finetuning depending on what is actually contained within each of the columns.

Option Explicit

Sub ExampleCaller()
Dim ws As Worksheet

    Set ws = ActiveSheet
    With ws
        'Call FillTheHoles(.Range(.Cells(3, 1), LastCell(ws)))
        'for testing
        Call FillTheHoles(Selection)
    End With
    Set ws = Nothing
End Sub

Sub FillTheHoles(Rng As Range)
Dim DataRngArr As Variant
Dim RowInd As Long    ' row index for looping
Dim ColInd As Long    ' column index for looping

    DataRngArr = Rng.Value2

    For ColInd = 1 To Rng.Columns.Count
        For RowInd = LBound(DataRngArr) To UBound(DataRngArr)
            If DataRngArr(RowInd, ColInd) = vbNullString Then
                DataRngArr(RowInd, ColInd) = DataRngArr(RowInd - 1, ColInd)
            End If
        Next RowInd
    Next ColInd

    With Rng
        ''to over-write the orginal values (uncomment after testing is complete)
        '.Value2 = DataRngArr
        'while testing
        .Offset(0, 7).Value2 = DataRngArr

        .Rows(1).Copy
        'Remove the offset section to over-write the existing values
        .Offset(0, 7).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                                   SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    End With

End Sub

Function LastCell(ws As Excel.Worksheet) As Excel.Range
' sourced from http://www.beyondtechnology.com/geeks012.shtml
'Obj: to identify the lastcell on a worksheet (& not necessarily the active sheet)
Dim LastRow As Long
Dim LastCol As Long
    ' Error-handling is here in case there is not any
    ' data in the worksheet
    On Error Resume Next
    With ws
        ' Find the last real populated row
        LastRow = .Cells.Find(What:="*", _
                              SearchDirection:=xlPrevious, _
                              SearchOrder:=xlByRows).Row
        ' Find the last real populated column
        LastCol = .Cells.Find(What:="*", _
                              SearchDirection:=xlPrevious, _
                              SearchOrder:=xlByColumns).Column
        ' Finally, initialize a Range object variable for
        ' the last populated row.
        Set LastCell = .Cells(LastRow, LastCol)
        If LastCell Is Nothing Then Set LastCell = .Cells(1, 1)
    End With
    On Error GoTo 0
End Function

Open in new window


hth
Rob
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Rob HensonFinance AnalystCommented:
For a non-VBA approach, use the AutoFilter feature.

Select the data and apply the AutoFilter. On the column with the blanks select the dropdown and choose {Blanks}. This will leave those that are empty or contain only spaces visible.

In the first visible cell enter a formula referring to cell above, I am assuming column G and first blank row is row 3:

=G2

Now use shift and arrow keys to select this cell and the remaining blank cells in the column. Press Ctrl + D to fill down. This will only populate the visible cells.

You can then disable the Auto Filter and copy and paste values if so required.

Thanks
Rob H
0
broro183Commented:
hi,

I considered the auto-filter approach too (as Rob H has suggested in the previous post), but I am concerned about the specialcells limitation (8,192 non-contiguous cell areas) which may be a problem due to the size of the data set (~90k+). I haven't tested it, but I'm guessing that it could be a problem when using autofilter* unless Kumara is using excel 2010+.
*(sorting could create a single contiguous range, although it looks like the mere act of sorting would ruin the integrity of the data)

Here are some informative links:
http://dmcritchie.mvps.org/excel/proper.htm
http://www.rondebruin.nl/win/s4/win003.htm
http://support.microsoft.com/default.aspx?scid=kb;en-us;832293

hth
Rob
0
kumarasinghAuthor Commented:
Thanks Guys. Appreciate all your help. Used the macro suggested by Rgonzo1971 and it's working fine.
0
broro183Commented:
hi Kumara,

Not for points.

How long does it take to run the macro from Rgonzo1971 on your machine?
On my machine it is ~2-2.5 minutes (because it "hits" each cell individually which makes the code run slower).

I have modified my earlier code & on my machine it takes a few seconds to run over the sample file from your original post.

Option Explicit
Sub FillTheHoles_v2()
Dim ws As Worksheet
Dim Rng As Range
Dim DataRngArr As Variant
Dim RowInd As Long    ' row index for looping
Dim ColInd As Long    ' column index for looping

    Debug.Print Now

    Set ws = ActiveSheet
    With ws
        Set Rng = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With

    DataRngArr = Rng.Value2

    For ColInd = 1 To Rng.Columns.Count
        For RowInd = LBound(DataRngArr) To UBound(DataRngArr)
            If DataRngArr(RowInd, ColInd) = vbNullString Then
                DataRngArr(RowInd, ColInd) = DataRngArr(RowInd - 1, ColInd)
            End If
        Next RowInd
    Next ColInd

    With Rng
        .Value2 = DataRngArr
        .NumberFormat = "dd/mm/yyyy hh:mm"
    End With
    
    Set Rng = Nothing
    Set ws = Nothing

    Debug.Print Now

End Sub

Open in new window


Hth
Rob
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.