Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill colum to the next non blank row
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)? -- --coastal |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill colum to the next non blank row
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) "coastal" wrote in message ... 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)? -- --coastal |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill colum to the next non blank row
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) "Chip Pearson" wrote in message ... 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) "coastal" wrote in message ... 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)? -- --coastal |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill colum to the next non blank row
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 -- Gary''s Student - gsnu200731 "coastal" wrote: 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)? -- --coastal |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill colum to the next non blank row
Thanks that worked like a charm!
-- --coastal "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200731 "coastal" wrote: 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)? -- --coastal |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill colum to the next non blank row
You are very welcome
-- Gary''s Student - gsnu200731 "coastal" wrote: Thanks that worked like a charm! -- --coastal "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200731 "coastal" wrote: 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)? -- --coastal |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill colum to the next non blank row
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 "coastal" wrote: 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)? -- --coastal |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill colum to the next non blank row
And the winner is..................Teethless Mama
No macros, no formulas to deal with. Gord Dibben MS Excel MVP On Tue, 19 Jun 2007 15:28:24 -0700, Teethless mama wrote: 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 "coastal" wrote: 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)? -- --coastal |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill colum to the next non blank row
Hello,
What does it mean "then point the arrow up". I can't figure it out. Thank you. "Teethless mama" wrote: 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 "coastal" wrote: 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)? -- --coastal |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill colum to the next non blank row
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 "Blueglass" wrote in message ... Hello, What does it mean "then point the arrow up". I can't figure it out. Thank you. "Teethless mama" wrote: 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 "coastal" wrote: 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)? -- --coastal |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill colum to the next non blank row
You missed out step of typing the = sign in active blank cell before hitting
the up arrow. Gord On Thu, 15 Nov 2007 10:52:58 -0800, "Peo Sjoblom" wrote: 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill colum to the next non blank row
Awesome! Works like a charm!
"Gord Dibben" wrote: You missed out step of typing the = sign in active blank cell before hitting the up arrow. Gord On Thu, 15 Nov 2007 10:52:58 -0800, "Peo Sjoblom" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill in the blank | Excel Discussion (Misc queries) | |||
colum 1 = product #'s than colum 2 = prod. disc. | Excel Discussion (Misc queries) | |||
Keeping a sum colum correct after inserting a colum of data in fro | Excel Discussion (Misc queries) | |||
Check data on colum A and find match on colum b | Excel Discussion (Misc queries) | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) |