Fill colum to the next non blank row

C

coastal

Ok,
Not sue how to explain this so I will just describe it:

Here is an shortened sample of the file that I am working with:
1,New York, Joe Smith, AddressA
2,(null), Mary Joe, AddressB
3,(null), Jim Bob, AddressC
4,California, Billy Joel, AddressD
5,(null), Jane Doe, AddressE
6,(null), Mary Jane, AddressF

What I need to do is fill the (null) cells with the title above it (so rows
2 & 3 should have New York in the (null) cell. Problem is there are hundreds
of rows. Is there a way to fill a column so that the text "New York" is
filled up only to the row that contains more text (in this case California,
row 4)?
 
C

Chip Pearson

Here's a simple little macro that will do the job. Select the range whose
blank cells you want to fill. In your example data, this would be the range
beginning with the first "New York" record and then downward through the
last null row of the "California" group. Then run the code.

Sub FillIn()
Dim Rng As Range
Dim V As Variant

For Each Rng In Selection.Cells
If Rng.Text <> vbNullString Then
V = Rng.Text
Else
Rng.Value = V
End If
Next Rng
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
C

Chip Pearson

If you want a non-VBA method, insert a column next to your original data and
enter the following formula:

=IF(ROW(A1)>1,IF(A1="",OFFSET(A1,-1,0),A1),A1)

Change "A1" to the first data cell. Then, copy this formula down as far as
your data goes. If desired, you can copy the value of this new column and do
a Paste Special Values from the Edit menu to overwrite the existing data.
Then you can delete the column you just added.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
G

Gary''s Student

Let's use a helper column say col D.

In D1 enter:
=A1
In D2 enter:
=IF(A2="",D1,A2) and copy down the column

This will look like column A with the blanks filled in.

Finally copy col D and paste/special as value back onto column A.

At this point col D can be cleared
 
C

coastal

Thanks that worked like a charm!
--
--coastal


Gary''s Student said:
Let's use a helper column say col D.

In D1 enter:
=A1
In D2 enter:
=IF(A2="",D1,A2) and copy down the column

This will look like column A with the blanks filled in.

Finally copy col D and paste/special as value back onto column A.

At this point col D can be cleared
Click to expand...
 
T

Teethless mama

Assuming header in row 1, and states in column A2:A100
select A2:A100 > hit F5 > Special > Blanks > OK > enter equal sign = then
point the arrow up > press ctrl key and hit enter
 
G

Gord Dibben

And the winner is..................Teethless Mama

No macros, no formulas to deal with.


Gord Dibben MS Excel MVP
 
B

Blueglass

Hello,
What does it mean "then point the arrow up". I can't figure it out.
Thank you.
 
P

Peo Sjoblom

after select the blanks using F5 hit the up arrow key on your keyboard and
press ctrl + enter

will fill the blanks with whatever is in the cells above them


--


Regards,


Peo Sjoblom
 
G

Gord Dibben

You missed out step of typing the = sign in active blank cell before hitting
the up arrow.


Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

don't know what function to use 2
do loop through a table 2
Insert Cells every other row 1
INSERT 3 ROWS WHEN DATA OF COLUM CHANGED 2
Auto-update Fill Series in column that has randomly spaced blank c 3
Combine multiple columns and rows for one record into one row. 2
Excel Popup box to fill a row 0
Looking forMacro to do this Sheet to Sheet copy &sort 0

Top