Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Hi all
I have looked through the forums but can't find what i need. In each row that column N (Sheet name is Sales) is empty I want to copy columns A:T to another worksheet (Sheet Name is Carryovers) starting at A2 Hope this is clear enough many thanks in advance Alan ajm1949 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
This might work
Sub terrain() Dim sh1 As Worksheet, sh2 As Worksheet Dim lr1 As Long, lr2 As Long, rng As Range Set sh1 = Sheets("Sales") Set sh2 = Sheets("Carryovers") lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh1.Range("N1:N" & lr) For Each c In rng If c.Value = "" Then Range("A" & c.Row).Resize(1, 21).Copy _ sh2.Range("A" & sh2.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row) End If Next End Sub "ajm1949" wrote in message ... Hi all I have looked through the forums but can't find what i need. In each row that column N (Sheet name is Sales) is empty I want to copy columns A:T to another worksheet (Sheet Name is Carryovers) starting at A2 Hope this is clear enough many thanks in advance Alan ajm1949 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Many Thanks
It works perfectly Cheers Alan "JLGWhiz" wrote: This might work Sub terrain() Dim sh1 As Worksheet, sh2 As Worksheet Dim lr1 As Long, lr2 As Long, rng As Range Set sh1 = Sheets("Sales") Set sh2 = Sheets("Carryovers") lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh1.Range("N1:N" & lr) For Each c In rng If c.Value = "" Then Range("A" & c.Row).Resize(1, 21).Copy _ sh2.Range("A" & sh2.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row) End If Next End Sub "ajm1949" wrote in message ... Hi all I have looked through the forums but can't find what i need. In each row that column N (Sheet name is Sales) is empty I want to copy columns A:T to another worksheet (Sheet Name is Carryovers) starting at A2 Hope this is clear enough many thanks in advance Alan ajm1949 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Here is a considerably shorter (and I believe much faster) macro which does
what your macro does... Sub terranean() On Error Resume Next Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _ Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow).Copy Sheets("Carryovers").Range("A2") End Sub -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... This might work Sub terrain() Dim sh1 As Worksheet, sh2 As Worksheet Dim lr1 As Long, lr2 As Long, rng As Range Set sh1 = Sheets("Sales") Set sh2 = Sheets("Carryovers") lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh1.Range("N1:N" & lr) For Each c In rng If c.Value = "" Then Range("A" & c.Row).Resize(1, 21).Copy _ sh2.Range("A" & sh2.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row) End If Next End Sub "ajm1949" wrote in message ... Hi all I have looked through the forums but can't find what i need. In each row that column N (Sheet name is Sales) is empty I want to copy columns A:T to another worksheet (Sheet Name is Carryovers) starting at A2 Hope this is clear enough many thanks in advance Alan ajm1949 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Rick Rothstein was thinking very hard :
Here is a considerably shorter (and I believe much faster) macro which does what your macro does... Sub terranean() On Error Resume Next Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _ Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow).Copy Sheets("Carryovers").Range("A2") End Sub -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... This might work Sub terrain() Dim sh1 As Worksheet, sh2 As Worksheet Dim lr1 As Long, lr2 As Long, rng As Range Set sh1 = Sheets("Sales") Set sh2 = Sheets("Carryovers") lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh1.Range("N1:N" & lr) For Each c In rng If c.Value = "" Then Range("A" & c.Row).Resize(1, 21).Copy _ sh2.Range("A" & sh2.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row) End If Next End Sub "ajm1949" wrote in message ... Hi all I have looked through the forums but can't find what i need. In each row that column N (Sheet name is Sales) is empty I want to copy columns A:T to another worksheet (Sheet Name is Carryovers) starting at A2 Hope this is clear enough many thanks in advance Alan ajm1949 Now, Rick, I thought I was going to beat you to this one. Not surprised I didn't, ..just a bit disappointed in myself for not getting my reply finalized sooner. (It's been a distracting day today) Anyway, I just thought I'd let you know that I'm learning a lot from your posts, and so my disappoinment is offset by the discovery that I was about to duplicate what you posted. Credit goes to you for that! Thanks for all the good learning content that you post! regards, Garry |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Hello Rick and Garry
Seems like you guys are a bit competitive...Keep up the great work. I am also learning a lot from all the posts here. The first code sample had a minor error. Sub MoveCarryOvers() Dim sh1 As Worksheet, sh2 As Worksheet Dim lr1 As Long, rng As Range Dim c As Object Set sh1 = Sheets("Sales") Set sh2 = Sheets("Carry overs") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh1.Range("N1:N" & lr1) For Each c In rng If c.Value = "" Then Range("A" & c.Row).Resize(1, 21).Copy _ sh2.Range("A" & sh2.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row) End If Next End Sub Rick's sample definitely runs noticably faster. Thank Rick. My next question is this. How do I clear rows with an entry in column N? Cheers Alan "GS" wrote: Rick Rothstein was thinking very hard : Here is a considerably shorter (and I believe much faster) macro which does what your macro does... Sub terranean() On Error Resume Next Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _ Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow).Copy Sheets("Carryovers").Range("A2") End Sub -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... This might work Sub terrain() Dim sh1 As Worksheet, sh2 As Worksheet Dim lr1 As Long, lr2 As Long, rng As Range Set sh1 = Sheets("Sales") Set sh2 = Sheets("Carryovers") lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh1.Range("N1:N" & lr) For Each c In rng If c.Value = "" Then Range("A" & c.Row).Resize(1, 21).Copy _ sh2.Range("A" & sh2.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row) End If Next End Sub "ajm1949" wrote in message ... Hi all I have looked through the forums but can't find what i need. In each row that column N (Sheet name is Sales) is empty I want to copy columns A:T to another worksheet (Sheet Name is Carryovers) starting at A2 Hope this is clear enough many thanks in advance Alan ajm1949 Now, Rick, I thought I was going to beat you to this one. Not surprised I didn't, ..just a bit disappointed in myself for not getting my reply finalized sooner. (It's been a distracting day today) Anyway, I just thought I'd let you know that I'm learning a lot from your posts, and so my disappoinment is offset by the discovery that I was about to duplicate what you posted. Credit goes to you for that! Thanks for all the good learning content that you post! regards, Garry . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Hi Alan,
Rick and I aren't competing; he's the veteran, I'm the newbie. That said, I like his coding and aspire to be as good as he is some day, and so the competition is with myself and my VB[A} skills. <<How do I clear rows with an entry in column N? What do you mean by 'clear rows'? Do you mean ClearContents or Delete? Do you want to do it during the MoveCarryOvers routine OR after that routine is done. (During will be a bit tricky if deleting rows) Basically it would involve repeating the process with ClearContents OR EntireRow.Delete Garry -- It happens that ajm1949 formulated : Hello Rick and Garry Seems like you guys are a bit competitive...Keep up the great work. I am also learning a lot from all the posts here. The first code sample had a minor error. Sub MoveCarryOvers() Dim sh1 As Worksheet, sh2 As Worksheet Dim lr1 As Long, rng As Range Dim c As Object Set sh1 = Sheets("Sales") Set sh2 = Sheets("Carry overs") lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh1.Range("N1:N" & lr1) For Each c In rng If c.Value = "" Then Range("A" & c.Row).Resize(1, 21).Copy _ sh2.Range("A" & sh2.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row) End If Next End Sub Rick's sample definitely runs noticably faster. Thank Rick. My next question is this. How do I clear rows with an entry in column N? Cheers Alan "GS" wrote: Rick Rothstein was thinking very hard : Here is a considerably shorter (and I believe much faster) macro which does what your macro does... Sub terranean() On Error Resume Next Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _ Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow).Copy Sheets("Carryovers").Range("A2") End Sub -- Rick (MVP - Excel) "JLGWhiz" wrote in message ... This might work Sub terrain() Dim sh1 As Worksheet, sh2 As Worksheet Dim lr1 As Long, lr2 As Long, rng As Range Set sh1 = Sheets("Sales") Set sh2 = Sheets("Carryovers") lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh1.Range("N1:N" & lr) For Each c In rng If c.Value = "" Then Range("A" & c.Row).Resize(1, 21).Copy _ sh2.Range("A" & sh2.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row) End If Next End Sub "ajm1949" wrote in message ... Hi all I have looked through the forums but can't find what i need. In each row that column N (Sheet name is Sales) is empty I want to copy columns A:T to another worksheet (Sheet Name is Carryovers) starting at A2 Hope this is clear enough many thanks in advance Alan ajm1949 Now, Rick, I thought I was going to beat you to this one. Not surprised I didn't, ..just a bit disappointed in myself for not getting my reply finalized sooner. (It's been a distracting day today) Anyway, I just thought I'd let you know that I'm learning a lot from your posts, and so my disappoinment is offset by the discovery that I was about to duplicate what you posted. Credit goes to you for that! Thanks for all the good learning content that you post! regards, Garry . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Anyway, I just thought I'd let you know that I'm learning a
lot from your posts, I am glad you are able to get some useful ideas and techniques from the postings I provide on these newsgroups and that you are able to incorporate them in your own work. Given that, you will probably find the approach I used in my last posting back to the OP of some interest. Since you find my postings instructive in some small ways, I just want to give you a heads up that there is a chance I will cease my volunteer efforts starting in October. I have a strong feeling that I will not have my MVP status renewed when it comes up for renewal in October. I'm sure you, as well as others, question what my having MVP status would have to do with my continuing to volunteer answering questions on newsgroups which, of course, I can do with or without the MVP recognition. This is true, but my volunteering efforts take up a lot of my time... time I could be using to pursue some of my many other interests. So, you will probably ask, why don't I just stop now? The MVP award is given for activities performed in the year prior to its being awarded. This has never made sense to me... after being given the honor of being able to sign myself as an MVP, there is no obligation to continuing doing a "good job" afterwards. Well, I have an internal "something" that won't let me do that. I figure that if I have been given the honor of being able to sign myself as an MVP, then I should continue trying to perform at the same level that won me the honor in the first place. So, while this is not Microsoft's position, I **personally** consider receiving the honor as binding on me to continue performing at the same level (or better) as I did the year before. If I should not be re-awarded in October, as I suspect will probably happen, then I will consider myself free of this "self-imposed" obligation and this, in return, would allow me to pursue those other activities I have put off for, oh, some eight years now. If I am re-awarded, then I'll be back as active as ever; but if I am not re-awarded, then I won't. As I said, just a heads up in case you need it. -- Rick (MVP - Excel) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Rick Rothstein submitted this idea :
Anyway, I just thought I'd let you know that I'm learning a lot from your posts, I am glad you are able to get some useful ideas and techniques from the postings I provide on these newsgroups and that you are able to incorporate them in your own work. Given that, you will probably find the approach I used in my last posting back to the OP of some interest. Since you find my postings instructive in some small ways, I just want to give you a heads up that there is a chance I will cease my volunteer efforts starting in October. I have a strong feeling that I will not have my MVP status renewed when it comes up for renewal in October. I'm sure you, as well as others, question what my having MVP status would have to do with my continuing to volunteer answering questions on newsgroups which, of course, I can do with or without the MVP recognition. This is true, but my volunteering efforts take up a lot of my time... time I could be using to pursue some of my many other interests. So, you will probably ask, why don't I just stop now? The MVP award is given for activities performed in the year prior to its being awarded. This has never made sense to me... after being given the honor of being able to sign myself as an MVP, there is no obligation to continuing doing a "good job" afterwards. Well, I have an internal "something" that won't let me do that. I figure that if I have been given the honor of being able to sign myself as an MVP, then I should continue trying to perform at the same level that won me the honor in the first place. So, while this is not Microsoft's position, I **personally** consider receiving the honor as binding on me to continue performing at the same level (or better) as I did the year before. If I should not be re-awarded in October, as I suspect will probably happen, then I will consider myself free of this "self-imposed" obligation and this, in return, would allow me to pursue those other activities I have put off for, oh, some eight years now. If I am re-awarded, then I'll be back as active as ever; but if I am not re-awarded, then I won't. As I said, just a heads up in case you need it. Yes, your last posting is very interesting indeed, and makes perfect sense to me. It seems my own thinking is forming a similar pattern after studying many of your postings. I think that's a good thing overall, but there's times when it's a challenge to get things where they fall into place as well as you seem to put them. I'm determined to get there, though! Thanks for the 'heads up'! I completely understand where you're coming from. I suppose I should consider doing the same but it's hard to do most things anymore since I'm forced to spend most my waking hours in a wheelchair. (I have ALS ..$%^!) Anyway, between here and the VB forum I've learned so much from all you MVPs, and so I feel I owe it to the community to give something back. I'm a machinist/toolmaker by profession but since I can't work at that anymore I decided to teach myself programming (other than CNC programming) so I could turn some of my Excel solutions into something anyone could use. My formal ed during high school and college was business, majoring in accounting and bus. management. I worked as a cost accountant and forensic auditor for a fortune500 company after college, but decided I could put my formal ed to better use working for myself. (that was back in the late 60's!) Thus, I made a business of working at my hobby. I started using Excel in v4 while I was teaching at community college. (They were switching to M$O from Corel Suite) I wish I had put more time into it back then but I had a business to look after when I wasn't teaching. I started using VBA on Thanksgiving weekend of 2003, when I picked up my first book by JWalk. In June 2004 I met Rob Bovey via email and have been a devotee of the Bovey, Bullen, Green movement ever since. I can't thank Rob enough for all the help and support he's given to my learning process. This also extends to all the 'vets' in the NGs. I've read 10s of thousands of postings and only this year did I start using a newsreader. Man.., I wish I had done that long, long ago! -website UI sucks compared to this! I've been worried that this NG shutdown fiasco might mean losing contact with the pros I've grown to respect over these past few years. I'm saddened by your heads up, frankly. Your contributions will be truly missed! I certainly will appreciate the remaining time you stick with it all that much more. However, I do sincerely wish you best of best wishes for your future plans. I promise I will be your fan for life, and I'll work diligently to prove myself worthy of this honor. Kind regards, Garry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy range from Sheet1 into empty range in Sheet2 | Excel Programming | |||
copy all but formula to next empty column | Excel Programming | |||
Copy data to next empty row in a range | Excel Programming | |||
Copy after autofilter if range is not empty | Excel Programming | |||
Copy from row above if cell is empty in column | Excel Discussion (Misc queries) |