| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help Please
Hello Group
I have a file that has data in cells A1:F1, A5:F5, A9:F9 etc. I would like to copy the data in the first row down and fill in the blanks where A2:F4 =A1:F1 A6:F8 = A5:F5, A10:F14=A9:F9 etc How do I do this? Thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Help Please
If this is a one-time need, I'd just create the formulas to do it...
Select your entire table (A1:F2000 or whatever). Turn on autofilters (Data Filter Autofilter) and select 'blanks' in the first dropdown. Since it's blank, row 2 should be visible. In A2 enter the formula =A1. Autofill that formula across to column F and then down to all the visible rows. Turn off your filter and all the blank rows should be there. If you need to 'lock down' the values (getting rid of the formulas we just entered), select the entire table and Copy / Paste Special values. "Al" wrote: Hello Group I have a file that has data in cells A1:F1, A5:F5, A9:F9 etc. I would like to copy the data in the first row down and fill in the blanks where A2:F4 =A1:F1 A6:F8 = A5:F5, A10:F14=A9:F9 etc How do I do this? Thanks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Help Please
Thanks for the response. Not a one time deal. I am trying to add to
existing code that is run in a regular basis. Thanks anyway! "bpeltzer" wrote: If this is a one-time need, I'd just create the formulas to do it... Select your entire table (A1:F2000 or whatever). Turn on autofilters (Data Filter Autofilter) and select 'blanks' in the first dropdown. Since it's blank, row 2 should be visible. In A2 enter the formula =A1. Autofill that formula across to column F and then down to all the visible rows. Turn off your filter and all the blank rows should be there. If you need to 'lock down' the values (getting rid of the formulas we just entered), select the entire table and Copy / Paste Special values. "Al" wrote: Hello Group I have a file that has data in cells A1:F1, A5:F5, A9:F9 etc. I would like to copy the data in the first row down and fill in the blanks where A2:F4 =A1:F1 A6:F8 = A5:F5, A10:F14=A9:F9 etc How do I do this? Thanks! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Help Please
Ok, then VBA it is...
Dim sourcerow For sourcerow = 1 To 9 Step 4 'fill in the appropriate start and end row#s Range(Cells(sourcerow, 1), Cells(sourcerow, 6)).Copy Range(Cells(sourcerow + 1, 1), Cells(sourcerow + 3, 6)).PasteSpecial _ Paste:=xlPasteValues Next "Al" wrote: Thanks for the response. Not a one time deal. I am trying to add to existing code that is run in a regular basis. Thanks anyway! "bpeltzer" wrote: If this is a one-time need, I'd just create the formulas to do it... Select your entire table (A1:F2000 or whatever). Turn on autofilters (Data Filter Autofilter) and select 'blanks' in the first dropdown. Since it's blank, row 2 should be visible. In A2 enter the formula =A1. Autofill that formula across to column F and then down to all the visible rows. Turn off your filter and all the blank rows should be there. If you need to 'lock down' the values (getting rid of the formulas we just entered), select the entire table and Copy / Paste Special values. "Al" wrote: Hello Group I have a file that has data in cells A1:F1, A5:F5, A9:F9 etc. I would like to copy the data in the first row down and fill in the blanks where A2:F4 =A1:F1 A6:F8 = A5:F5, A10:F14=A9:F9 etc How do I do this? Thanks! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Help Please
OK But row 9 is dynamic how do I incorporate last row?
Thanks "bpeltzer" wrote: Ok, then VBA it is... Dim sourcerow For sourcerow = 1 To 9 Step 4 'fill in the appropriate start and end row#s Range(Cells(sourcerow, 1), Cells(sourcerow, 6)).Copy Range(Cells(sourcerow + 1, 1), Cells(sourcerow + 3, 6)).PasteSpecial _ Paste:=xlPasteValues Next "Al" wrote: Thanks for the response. Not a one time deal. I am trying to add to existing code that is run in a regular basis. Thanks anyway! "bpeltzer" wrote: If this is a one-time need, I'd just create the formulas to do it... Select your entire table (A1:F2000 or whatever). Turn on autofilters (Data Filter Autofilter) and select 'blanks' in the first dropdown. Since it's blank, row 2 should be visible. In A2 enter the formula =A1. Autofill that formula across to column F and then down to all the visible rows. Turn off your filter and all the blank rows should be there. If you need to 'lock down' the values (getting rid of the formulas we just entered), select the entire table and Copy / Paste Special values. "Al" wrote: Hello Group I have a file that has data in cells A1:F1, A5:F5, A9:F9 etc. I would like to copy the data in the first row down and fill in the blanks where A2:F4 =A1:F1 A6:F8 = A5:F5, A10:F14=A9:F9 etc How do I do this? Thanks! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Help Please
Whatever your variables are, you can just change the conditions on the For.
Alternately, you could create something like Dim sourcerow sourcerow = 1 While Cells(sourcerow, 1) < "" Range(Cells(sourcerow, 1), Cells(sourcerow, 6)).Copy Range(Cells(sourcerow + 1, 1), Cells(sourcerow + 3, 6)).PasteSpecial Paste:=xlPasteValues sourcerow = sourcerow + 4 Wend This just continues until it finds column A blank in the next sourcerow. "Al" wrote: OK But row 9 is dynamic how do I incorporate last row? Thanks "bpeltzer" wrote: Ok, then VBA it is... Dim sourcerow For sourcerow = 1 To 9 Step 4 'fill in the appropriate start and end row#s Range(Cells(sourcerow, 1), Cells(sourcerow, 6)).Copy Range(Cells(sourcerow + 1, 1), Cells(sourcerow + 3, 6)).PasteSpecial _ Paste:=xlPasteValues Next "Al" wrote: Thanks for the response. Not a one time deal. I am trying to add to existing code that is run in a regular basis. Thanks anyway! "bpeltzer" wrote: If this is a one-time need, I'd just create the formulas to do it... Select your entire table (A1:F2000 or whatever). Turn on autofilters (Data Filter Autofilter) and select 'blanks' in the first dropdown. Since it's blank, row 2 should be visible. In A2 enter the formula =A1. Autofill that formula across to column F and then down to all the visible rows. Turn off your filter and all the blank rows should be there. If you need to 'lock down' the values (getting rid of the formulas we just entered), select the entire table and Copy / Paste Special values. "Al" wrote: Hello Group I have a file that has data in cells A1:F1, A5:F5, A9:F9 etc. I would like to copy the data in the first row down and fill in the blanks where A2:F4 =A1:F1 A6:F8 = A5:F5, A10:F14=A9:F9 etc How do I do this? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
| Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
| how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
| macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
| Start Macro / Stop Macro / Restart Macro | Excel Programming | |||
Hybrid Mode
