| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Don't Copy If Null -- Help
I have generated a code with help of macro's that will select a range
and then Auto fills it to a given range. It all works fine with one exception. What I really like to do is stop copying the formula if there is no values in Column A, in other word do copy until cell in Column A is Null, Starting from A3. Range("C2:BL2").Select Selection.AutoFill Destination:=Range("C2:BL43"), Type:=xlFillDefault Range("C2:BL43").Select A B C D E F Etc ----------- 1 NAME Formula Formula Formula 2 Name Ardy |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Don't Copy If Null -- Help
Can you start at the bottom of column A and come up to find that last used cell?
dim LastRow as long with activesheet lastrow = .cells(.rows.count,"A").end(xlup).row .range("c2:bl2").autofill destination:=.range("c2:bl" & lastrow), _ type:=xlfilldefault end with Ardy wrote: I have generated a code with help of macro's that will select a range and then Auto fills it to a given range. It all works fine with one exception. What I really like to do is stop copying the formula if there is no values in Column A, in other word do copy until cell in Column A is Null, Starting from A3. Range("C2:BL2").Select Selection.AutoFill Destination:=Range("C2:BL43"), Type:=xlFillDefault Range("C2:BL43").Select A B C D E F Etc ----------- 1 NAME Formula Formula Formula 2 Name Ardy -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Don't Copy If Null -- Help
Peter:
The Problem is that I am inserting the first row of the formula to transfer some data points from other Tabs to C2:BL2 , Then Copying via Auto fill from C3:BL3 until I see a Null in Column A Starting from A3, So I really need to start from top until I see Null In Column A then Stop Sorry I responded late X-mas Stuff..... Ardy Dave Peterson wrote: Can you start at the bottom of column A and come up to find that last used cell? dim LastRow as long with activesheet lastrow = .cells(.rows.count,"A").end(xlup).row .range("c2:bl2").autofill destination:=.range("c2:bl" & lastrow), _ type:=xlfilldefault end with Ardy wrote: I have generated a code with help of macro's that will select a range and then Auto fills it to a given range. It all works fine with one exception. What I really like to do is stop copying the formula if there is no values in Column A, in other word do copy until cell in Column A is Null, Starting from A3. Range("C2:BL2").Select Selection.AutoFill Destination:=Range("C2:BL43"), Type:=xlFillDefault Range("C2:BL43").Select A B C D E F Etc ----------- 1 NAME Formula Formula Formula 2 Name Ardy -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Don't Copy If Null -- Help
Maybe you could use:
lastrow = .cells(1,"A").end(xldown).row Ardy wrote: Peter: The Problem is that I am inserting the first row of the formula to transfer some data points from other Tabs to C2:BL2 , Then Copying via Auto fill from C3:BL3 until I see a Null in Column A Starting from A3, So I really need to start from top until I see Null In Column A then Stop Sorry I responded late X-mas Stuff..... Ardy Dave Peterson wrote: Can you start at the bottom of column A and come up to find that last used cell? dim LastRow as long with activesheet lastrow = .cells(.rows.count,"A").end(xlup).row .range("c2:bl2").autofill destination:=.range("c2:bl" & lastrow), _ type:=xlfilldefault end with Ardy wrote: I have generated a code with help of macro's that will select a range and then Auto fills it to a given range. It all works fine with one exception. What I really like to do is stop copying the formula if there is no values in Column A, in other word do copy until cell in Column A is Null, Starting from A3. Range("C2:BL2").Select Selection.AutoFill Destination:=Range("C2:BL43"), Type:=xlFillDefault Range("C2:BL43").Select A B C D E F Etc ----------- 1 NAME Formula Formula Formula 2 Name Ardy -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Don't Copy If Null -- Help
Peter:
I am Sorry...... IIt works just as same from the bottom, I didn't think It Would. This was so Easy... Thanks Ardy wrote: Peter: The Problem is that I am inserting the first row of the formula to transfer some data points from other Tabs to C2:BL2 , Then Copying via Auto fill from C3:BL3 until I see a Null in Column A Starting from A3, So I really need to start from top until I see Null In Column A then Stop Sorry I responded late X-mas Stuff..... Ardy Dave Peterson wrote: Can you start at the bottom of column A and come up to find that last used cell? dim LastRow as long with activesheet lastrow = .cells(.rows.count,"A").end(xlup).row .range("c2:bl2").autofill destination:=.range("c2:bl" & lastrow), _ type:=xlfilldefault end with Ardy wrote: I have generated a code with help of macro's that will select a range and then Auto fills it to a given range. It all works fine with one exception. What I really like to do is stop copying the formula if there is no values in Column A, in other word do copy until cell in Column A is Null, Starting from A3. Range("C2:BL2").Select Selection.AutoFill Destination:=Range("C2:BL43"), Type:=xlFillDefault Range("C2:BL43").Select A B C D E F Etc ----------- 1 NAME Formula Formula Formula 2 Name Ardy -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Don't Copy If Null -- Help
Dave.
Ardy wrote: Peter: I am Sorry...... IIt works just as same from the bottom, I didn't think It Would. This was so Easy... Thanks Ardy wrote: Peter: The Problem is that I am inserting the first row of the formula to transfer some data points from other Tabs to C2:BL2 , Then Copying via Auto fill from C3:BL3 until I see a Null in Column A Starting from A3, So I really need to start from top until I see Null In Column A then Stop Sorry I responded late X-mas Stuff..... Ardy Dave Peterson wrote: Can you start at the bottom of column A and come up to find that last used cell? dim LastRow as long with activesheet lastrow = .cells(.rows.count,"A").end(xlup).row .range("c2:bl2").autofill destination:=.range("c2:bl" & lastrow), _ type:=xlfilldefault end with Ardy wrote: I have generated a code with help of macro's that will select a range and then Auto fills it to a given range. It all works fine with one exception. What I really like to do is stop copying the formula if there is no values in Column A, in other word do copy until cell in Column A is Null, Starting from A3. Range("C2:BL2").Select Selection.AutoFill Destination:=Range("C2:BL43"), Type:=xlFillDefault Range("C2:BL43").Select A B C D E F Etc ----------- 1 NAME Formula Formula Formula 2 Name Ardy -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Failed to save table attributes of (null) into (null). | Excel Discussion (Misc queries) | |||
| COUNTIF says Null = Blank but Blank < Null | Excel Worksheet Functions | |||
| Null, "null", vbNull, vbNullString, vbEmpty | Excel Programming | |||
| Copy contents of Cell to array based on Col1 value not null | Excel Programming | |||
| copy cell with non null value | Excel Programming | |||
Hybrid Mode
