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

nodroj81

Board Regular
Joined
Mar 21, 2008
Messages
126
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?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi, try this macro:
Code:
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
 
Upvote 0
Can you do it with this:confused:??
1. Select all of the data in column A, from top to bottom.
2. Click on Home->Find & Select->Go To Special
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.)
4. Press "=", and then press up to select cell A1.
5. Press CtrlEnter to copy the formula into all selected cells.

<COLGROUP><COL style="WIDTH: 594pt; mso-width-source: userset; mso-width-alt: 28160" width=792><TBODY>
</TBODY>
 
Upvote 0
Each time you wish to do this -- Just Highlight (Select) your Range(A1:B125), then Run this Macro:

Code:
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
 
Upvote 0
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
 
Upvote 0
@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:
Code:
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
 
Upvote 0
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!
 
Upvote 0
can you do it with this:confused:??
1. Select all of the data in column a, from top to bottom.
2. Click on home->find & select->go to special
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.)
4. Press "=", and then press up to select cell a1.
5. Press ctrlenter to copy the formula into all selected cells.

<tbody>
</tbody>


thank you so much for this
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top