Find First Non-empty Cell & Paste To Preceding Cells

blue3zero · Jun 14th 2007

I need to find the first non-empty cell in a range and paste a constant value into the preceding cells.

aa bb
aa bb
aa bb
aa bb cc
aa bb cc

aa bb xx
aa bb xx
aa bb xx
aa bb cc
aa bb cc

Arthur494 · Jun 15th 2007

Re: Find First Non-empty Cell And Paste A Value Into Preceding Cells

Hi, and welcome to the OzGrid!

I think you need to be a bit more explicit in your question:
The first non-empty cell which way? rows-then-columns or vice versa?
Preceding cells, how so?
Do you want to do this once, in Excel? or as part of a bigger VBA project?

Your example suggests that you just want to fill all the empty cells in the range with a constant.

One way could be to use

Code

Range("A1:C6").SpecialCells(xlCellTypeBlanks).Select

Rennie · Jun 15th 2007

Re: Find First Non-empty Cell And Paste A Value Into Preceding Cells

Assuming your data is in Range A1:A5 the following macro will replace the blank cells with "cc"s

aa bb
aa bb
aa bb
aa bb cc
aa bb cc

Code

Sub Copy_Blanks()


    Range("A1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.End(xlDown).Select
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Display More

Hope this helps.

Thanks,
Rennie

Dave Hawley · Jun 15th 2007

Re: Find First Non-empty Cell And Paste A Value Into Preceding Cells

Code

Sub DoiT()
    Range("A1").Copy Destination:=Range("C1", Range("C1").End(xlDown).Offset(-1, 0))
End Sub

blue3zero · Jun 15th 2007

Re: Find First Non-empty Cell & Paste To Preceding Cells

I have a sheet that is always layed out the same. Columns A through K with x amount of rows.

I have a macro that I recorded and modified a little that does a few things to format the sheet correctly and autofills columns A and C.
I need to find all of the blank cells in column K and fill it in with the value 'XXCXX'.

Here is the code of the macro that I have so far

Code

' Select Pricing Attributes Sheet
    Sheets("Pricing Attributes").Select
    CurRows = Selection.CurrentRegion.Rows.Count
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 1
    Range("A2").Select
    ActiveCell.FormulaR1C1 = PriceListID
    Selection.AutoFill Destination:=Range("A2:A" & CurRows)
    Range("A2:A" & CurRows).Select
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("C2:C3").Select
    Selection.AutoFill Destination:=Range("C2:C" & CurRows)
    Range("C2:C" & CurRows).Select
    Cells.Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    
    Range("K2:K" & CurRows).Select

Display More

blue3zero · Jun 15th 2007

Re: Find First Non-empty Cell & Paste To Preceding Cells

I added the following to the macro and it seems to have done the trick.
Does anyone see any danger to this method?

Code

Columns("K:K").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Replace What:="", Replacement:="XXCXX", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Find First Non-empty Cell & Paste To Preceding Cells

Participate now!

Share