Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill of empty cells
Select Columns A:C
do Edit=goto=special and select blanks. Assume C2 will be the activecell in the select. In the formula bar put in =C1 then do Ctrl+Enter to fill all the blank cells with this relative formula. The should then appear as you show. To convert to hard coded values, Select A:C, do Edit=Copy, then Edit=Paste special and select values. The key is to make the formula point to the cell above the activecell. -- Regards, Tom Ogilvy "Marc" wrote in message ... Dear community, I have the following problem that I would like to solve with a macro type solution. I do export data from an application into an Excel spreadsheet. Unfortunately, the export is done in a way that not each row contains data in every cell (NULL values). What I need is an AutoFill functionality that looks up the first entry in a data array and copy this to the other cells. Here is an example: Row 1: A 100 John Doe 1500.00 Row 2: 700.00 Row 3: B 110 Jane Doe 600.00 Row 4: 300.00 Row 5: 500.00 Row 6: C 120 Jimmy Lee 400.00 Now, row 2 should look like this and Row 4 and 5 accordingly: Row 2: A 100 John Doe 700.00 Row 4: B 110 Jane Doe 300.00 Row 5: B 110 Jane Doe 500.00 Any help is very much appreciated! Marc |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill of empty cells
Thanks Tom, this is really cool!
Regards, Marc "Tom Ogilvy" wrote: Select Columns A:C do Edit=goto=special and select blanks. Assume C2 will be the activecell in the select. In the formula bar put in =C1 then do Ctrl+Enter to fill all the blank cells with this relative formula. The should then appear as you show. To convert to hard coded values, Select A:C, do Edit=Copy, then Edit=Paste special and select values. The key is to make the formula point to the cell above the activecell. -- Regards, Tom Ogilvy "Marc" wrote in message ... Dear community, I have the following problem that I would like to solve with a macro type solution. I do export data from an application into an Excel spreadsheet. Unfortunately, the export is done in a way that not each row contains data in every cell (NULL values). What I need is an AutoFill functionality that looks up the first entry in a data array and copy this to the other cells. Here is an example: Row 1: A 100 John Doe 1500.00 Row 2: 700.00 Row 3: B 110 Jane Doe 600.00 Row 4: 300.00 Row 5: 500.00 Row 6: C 120 Jimmy Lee 400.00 Now, row 2 should look like this and Row 4 and 5 accordingly: Row 2: A 100 John Doe 700.00 Row 4: B 110 Jane Doe 300.00 Row 5: B 110 Jane Doe 500.00 Any help is very much appreciated! Marc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill of empty cells
thank you mark, you saved my whole afternoon
victor "Marc" wrote: Thanks Tom, this is really cool! Regards, Marc "Tom Ogilvy" wrote: Select Columns A:C do Edit=goto=special and select blanks. Assume C2 will be the activecell in the select. In the formula bar put in =C1 then do Ctrl+Enter to fill all the blank cells with this relative formula. The should then appear as you show. To convert to hard coded values, Select A:C, do Edit=Copy, then Edit=Paste special and select values. The key is to make the formula point to the cell above the activecell. -- Regards, Tom Ogilvy "Marc" wrote in message ... Dear community, I have the following problem that I would like to solve with a macro type solution. I do export data from an application into an Excel spreadsheet. Unfortunately, the export is done in a way that not each row contains data in every cell (NULL values). What I need is an AutoFill functionality that looks up the first entry in a data array and copy this to the other cells. Here is an example: Row 1: A 100 John Doe 1500.00 Row 2: 700.00 Row 3: B 110 Jane Doe 600.00 Row 4: 300.00 Row 5: 500.00 Row 6: C 120 Jimmy Lee 400.00 Now, row 2 should look like this and Row 4 and 5 accordingly: Row 2: A 100 John Doe 700.00 Row 4: B 110 Jane Doe 300.00 Row 5: B 110 Jane Doe 500.00 Any help is very much appreciated! Marc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want the autofill/drag function to skip empty cells | Excel Discussion (Misc queries) | |||
Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
How can I convert empty strings to empty cells? | Excel Discussion (Misc queries) | |||
Autofill: Need to autofill one week block, (5) weekday only into cells. | Excel Discussion (Misc queries) | |||
Can blank cells created using empty Double-Quotes not be empty?? | Excel Programming |