Macro to copy cell value down until next non-blank cell

[nodroj81]

Hi all,

Let me lay out my problem so you can understand what I'm trying to accomplish.

In a given sheet (Sheet1), I have two columns,

A1 and B1 are column headings

column A has names, so let's say A2 = John, A17 = Fred, A47 = Larry, and A91 = Zeus. All cells between the cells mentioned are blank.

Column B has order numbers, and every cell from B2 - B125 has an order number in it.

So my array in question is A1:B125.

What I am trying to do is find a macro that will look in column A and if the cell is blank, copy the name from above. If the cell isn't blank then the cell remains unchanged. This is done until the last row of data is reached. Please note that in the example above the last row of data for column A would be A125 (not A91).

I can actually put together a working macro that would essentially insert a temp column, insert a formula, copy the values of that temp column and then delete the temp column. That just seems like an incredibly inefficient way of doing it.

Any ideas?

 

[Vidar]

Hi, try this macro:

Sub FillRow()
    Dim Name As String
    For Each c In Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
        If Cells(c.Row, 1) > "" Then
            Name = Cells(c.Row, 1)
        Else
            Cells(c.Row, 1).Value = Name
        End If
    Next
End Sub

Vidar

 

[MickG]

Can you do it with this??
[TABLE="width: 594"]
<COLGROUP><COL style="WIDTH: 594pt; mso-width-source: userset; mso-width-alt: 28160" width=792><TBODY>[TR]
[TD="class: xl24, width: 792, bgcolor: transparent"]1. Select all of the data in column A, from top to bottom.[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]2. Click on Home->Find & Select->Go To Special[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]3. Click "Blanks" and hit "Ok". (At this point, all blanks in the column should be selected, and cell A2 should be the active cell.)[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]4. Press "=", and then press up to select cell A1.[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"]5. Press CtrlEnter to copy the formula into all selected cells.[/TD]
[/TR]
</TBODY>[/TABLE]

 

[jim may]

Each time you wish to do this -- Just Highlight (Select) your Range(A1:B125), then Run this Macro:

Sub XFillInBlanks()
Dim Rng1 As Range, Rng2 As Range
Set Rng1 = Selection
On Error Resume Next
Set Rng2 = Rng1.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not Rng2 Is Nothing Then
Rng2.FormulaR1C1 = "=R[-1]C"
With Rng1
.Value = .Value
End With
End If
End Sub

 

[nodroj81]

Thanks everybody for the quick and very helpful response.

@ jimmay, as far as I can tell this macro works great so thanks - I will be applying the macro to many different sheets (albeit with the same setup) so ideally I don't want to highlight the entire selection.

@MickG - very helpful resource and if I didn't need to do this action over and over again with multiple workbooks, then the suggestion would be perfect.

@Vidar - this is exactly what I was looking for. Is there a way to copy the format as well as the content?

Thanks again,


Jordon

 

[Vidar]

@Vidar - this is exactly what I was looking for. Is there a way to copy the format as well as the content?

Jordon

Hi Jordon
This one copies the format also:

Sub FillRow()
    Dim Name As String
    For Each c In Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
        If Cells(c.Row, 1) > "" Then
            Name = Cells(c.Row, 1).Value
            Cells(c.Row, 1).Copy
        Else
            Cells(c.Row, 1).Value = Name
            Cells(c.Row, 1).PasteSpecial (xlPasteFormats)
        End If
    Next
End Sub

Vidar

 

[ilieradum]

Hello jim May.
I saw your post about completet the blank cells from a sheet, and i was trying to to the job to my sheet with your macro. The problem i has is that i have 2 column B and C that has like 37000 rows. I want to fill the columns with below value but he didnt work. I dit like dis. I select the b2:C37000 and run the macros and he didn't fill the cells.
Can you please tell if the macro can be adjust o make the job, and if yes how can i adjust the macro.

Thanks you in advanced!

Good day!

 

[hardikparikh]

can you do it with this??
[table="width: 594"]
<tbody>[tr]
[td="class: Xl24, width: 792, bgcolor: Transparent"]1. Select all of the data in column a, from top to bottom.[/td]
[/tr]
[tr]
[td="class: Xl24, bgcolor: Transparent"]2. Click on home->find & select->go to special[/td]
[/tr]
[tr]
[td="class: Xl24, bgcolor: Transparent"]3. Click "blanks" and hit "ok". (at this point, all blanks in the column should be selected, and cell a2 should be the active cell.)[/td]
[/tr]
[tr]
[td="class: Xl24, bgcolor: Transparent"]4. Press "=", and then press up to select cell a1.[/td]
[/tr]
[tr]
[td="class: Xl24, bgcolor: Transparent"]5. Press ctrlenter to copy the formula into all selected cells.[/td]
[/tr]
</tbody>[/table]

thank you so much for this

 

[lanebailey]

New to Forum...thanks for this macro! It's saving me time in so many ways.

 

[MickG]

You're welcome