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
Hi Garry
I thought of it more a frienldy rivalry but it's great to have someone to look up to like Rick. if you are a newbie then i am still wearing nappies...LOL Perhaps i should explain in more detail. In short we need to clear the delivered cars from last months carryover sheet leaving undelivered cars before adding this months to the carryover sheet. Your code does this exactly how we need it. My first post was not quite correct in that I said moving to A2 was OK forgetting that some data may need to remain. Maybe Rick's code is faster but can it be modified to add data after the last used row? ClearContents (Columns A:T)would be better as sometimes there maybe other information after column T. This would be a separate operation before moving carryovers This is just a part of the end of month sales log update. I already have most of the other code in place. Steps are 1. Archive Sales sheet (create a copy and name it) 2 . Remove delivered cars from Carryovers Sheet 3. Move carryovers from sales 4. Clear sales sheet Many thanks to everyone who gives their time to help. Cheers Alan ajm1949 "GS" wrote: 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 . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Okay, let me see if I have this straight. There is data on the Sales sheet
in Columns A thru T and also in some columns after Column T as well. You want to move only those rows between Columns A thru T for those rows where Column N is empty over to the Carryovers sheet. When you are completely done, only those rows from your original data (all columns) on the Sales sheet where Column N is empty will remain on the Sales sheet (that is, at the end, all cells in Column N on the Sales sheet will be empty). If this is correct, give the following macro a try... Sub ProcessCarryovers() On Error Resume Next With Sheets("Sales") .Columns("N").SpecialCells(xlCellTypeBlanks).Entir eRow. _ Copy Sheets("Carryovers").Range("A2") .Range("A2", .Cells(.Rows.Count, .Columns.Count)).Clear End With With Sheets("Carryovers") .Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow.Copy Sheets("Sales").Range("A2") .Range("U2", .Cells(.Rows.Count, .Columns.Count)).Clear End With End Sub Since I'm not 100% sure of this, make sure you test the macro on a copy of your workbook (or, if you use your original workbook, make sure not to save it) until you are sure it does what you want. Just so you know, what this macro does is copy the **entire** row for the blank cells in Column N over to your Carryovers sheet, then it clears everything except the header row from the Sales sheet (you do have a header row on it, right?), then it copies the entire rows from the Carryovers sheet back to the Sales sheet, and then finally it clears Columns U thru to the last Column on the Carryovers sheet so that only the date in Columns A thru T remains. -- Rick (MVP - Excel) "ajm1949" wrote in message ... Hi Garry I thought of it more a frienldy rivalry but it's great to have someone to look up to like Rick. if you are a newbie then i am still wearing nappies...LOL Perhaps i should explain in more detail. In short we need to clear the delivered cars from last months carryover sheet leaving undelivered cars before adding this months to the carryover sheet. Your code does this exactly how we need it. My first post was not quite correct in that I said moving to A2 was OK forgetting that some data may need to remain. Maybe Rick's code is faster but can it be modified to add data after the last used row? ClearContents (Columns A:T)would be better as sometimes there maybe other information after column T. This would be a separate operation before moving carryovers This is just a part of the end of month sales log update. I already have most of the other code in place. Steps are 1. Archive Sales sheet (create a copy and name it) 2 . Remove delivered cars from Carryovers Sheet 3. Move carryovers from sales 4. Clear sales sheet Many thanks to everyone who gives their time to help. Cheers Alan ajm1949 "GS" wrote: 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 . . |
#10
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) |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Hi Rick
Thanks for the quick response. The sales sheets contains the current month sales and is emptied at the end of each month. No problems doing that. Before updating the carryover sheet we need to clear the delivered cars on that sheet(Column N will have data-salesperson's initials.) leaving the rows where column N is empty. First we need some code to do this. Then we need to copy the carryovers from the sales sheet to the last used row on the carryovers sheet. Garry's code sample does this part exactly how we want it but is slower than your sample ( not suitable as it overwrites existing data). Not sure if there is a faster macro to do what your example does but puts the data in the last used row. Good luck in your endeavours whatever the outcome of your MVP status is. My philosophy on voluntary activities is simple. If it stops being enjoyable then give it away. Cheers Alan "Rick Rothstein" wrote: Okay, let me see if I have this straight. There is data on the Sales sheet in Columns A thru T and also in some columns after Column T as well. You want to move only those rows between Columns A thru T for those rows where Column N is empty over to the Carryovers sheet. When you are completely done, only those rows from your original data (all columns) on the Sales sheet where Column N is empty will remain on the Sales sheet (that is, at the end, all cells in Column N on the Sales sheet will be empty). If this is correct, give the following macro a try... Sub ProcessCarryovers() On Error Resume Next With Sheets("Sales") .Columns("N").SpecialCells(xlCellTypeBlanks).Entir eRow. _ Copy Sheets("Carryovers").Range("A2") .Range("A2", .Cells(.Rows.Count, .Columns.Count)).Clear End With With Sheets("Carryovers") .Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow.Copy Sheets("Sales").Range("A2") .Range("U2", .Cells(.Rows.Count, .Columns.Count)).Clear End With End Sub Since I'm not 100% sure of this, make sure you test the macro on a copy of your workbook (or, if you use your original workbook, make sure not to save it) until you are sure it does what you want. Just so you know, what this macro does is copy the **entire** row for the blank cells in Column N over to your Carryovers sheet, then it clears everything except the header row from the Sales sheet (you do have a header row on it, right?), then it copies the entire rows from the Carryovers sheet back to the Sales sheet, and then finally it clears Columns U thru to the last Column on the Carryovers sheet so that only the date in Columns A thru T remains. -- Rick (MVP - Excel) "ajm1949" wrote in message ... Hi Garry I thought of it more a frienldy rivalry but it's great to have someone to look up to like Rick. if you are a newbie then i am still wearing nappies...LOL Perhaps i should explain in more detail. In short we need to clear the delivered cars from last months carryover sheet leaving undelivered cars before adding this months to the carryover sheet. Your code does this exactly how we need it. My first post was not quite correct in that I said moving to A2 was OK forgetting that some data may need to remain. Maybe Rick's code is faster but can it be modified to add data after the last used row? ClearContents (Columns A:T)would be better as sometimes there maybe other information after column T. This would be a separate operation before moving carryovers This is just a part of the end of month sales log update. I already have most of the other code in place. Steps are 1. Archive Sales sheet (create a copy and name it) 2 . Remove delivered cars from Carryovers Sheet 3. Move carryovers from sales 4. Clear sales sheet Many thanks to everyone who gives their time to help. Cheers Alan ajm1949 "GS" wrote: 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 . . . |
#12
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
See inline comments....
Thanks for the quick response. The sales sheets contains the current month sales and is emptied at the end of each month. No problems doing that. Okay, so you start off the month with a completely blank sheet (except for the column headers). Before updating the carryover sheet we need to clear the delivered cars on that sheet(Column N will have data-salesperson's initials.) leaving the rows where column N is empty. First we need some code to do this. Why? (See the rest of my comments before answering this.) Then we need to copy the carryovers from the sales sheet to the last used row on the carryovers sheet. For the code I posted, leaving the rows where Column N cells have entries in them does not interfere with moving the data for those rows where Column N cells are blank. What I am having trouble visualizing is what should be remaining on the Sales sheet **after** the data in Columns A thru T is moved to the Carryovers sheet. I thought you were saying the carried over data plus any other columns associated with it should remain (you mentioned something about data in columns after Column T). That is what the last macro I posted does. Can you clarify this point for me? The key thing from my standpoint (as someone who does not know your business model) is in understanding what you have on your two sheets before you start the macro and what you want to have on those sheets after the macro is done. I though I understood it all when I supplied my last macro, but now I am not so sure. So, if you can clarify what you have before the macro and what you what afterwards, that would be helpful. Garry's code sample I think you mean JLGWhiz's code sample... Garry did not post any code to this thread (I beat him to it<g). .....does this part exactly how we want it but is slower than your sample ( not suitable as it overwrites existing data). Not sure if there is a faster macro to do what your example does but puts the data in the last used row. Okay, I may have missed the "existing data" part in your earlier posts (mainly because you said to put the data starting in A2 on the Carryovers sheet). Are you saying the carryovers (Column N cells are empty) are cumulative over on the Carryovers sheet? That is, is there already data on the Carryovers sheet that the new data needs to be placed after? If so, it is easy enough to modify either of my codes to do that, but I don't know which of my macros you actually need. Once you provide the clarification I asked for above, I will know how to respond to you. Good luck in your endeavours whatever the outcome of your MVP status is. My philosophy on voluntary activities is simple. If it stops being enjoyable then give it away. Oh, don't get me wrong, answering Excel questions on newsgroups is most enjoyable to me, but there are other things that I find equally enjoyable as well but which I don't pursue because of the time commitment. I used to be a prolific contributor to various anagramming websites in the past (one site that cataloged some of my earlier work is http://www.anagramgenius.com/agasear...in&type=author and you can Google my name and the word anagrams to see others), but my efforts have been way down on this front for several years now; there is this president of a company I know that makes a programming language compiler who has been after me for years adopt his language and offer my "talents" (if that is the right word) in support of it; I enjoy photography (mostly family events) and using Photoshop to "fix" the pictures; and so on, and so on. There is lots I like to do and not enough time to do it all in. The MVP award has kept me tethered to the Microsoft world, but if I should lose that status (which for various reasons I think may happen), I would consider myself free to explore things outside of Excel. -- Rick (MVP - Excel) |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
See inline...
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. There are some (many?) who would question whether that is a good thing or not.<g 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. Hey, don't think for a moment the solutions I post come to me fully formed. The way my mind works is I see an "outline" (but no details) of the approach I think will work... I have to fight to get everything to work together correctly also. That part, to me, is the fun part of programming. I'm determined to get there, though! Desire is the first and most important step in accomplishing, well, pretty much anything. Since you have it, then I am sure you will succeed. 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 ..$%^!) I am really very sorry to hear this. I hope the progress of this terrible disease goes as well as possible for you. Anyway, between here and the VB forum I've learned so much from all you MVPs I used to be an MVP for the compiled version of the classic version of VB (from VB3 to VB6) before moving over to the Excel world. Perhaps you came across some of my earlier VB postings in your Google searches. ... and so I feel I owe it to the community to give something back. Your participation is most welcome. 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 was a math major in college. 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!) I worked for 32-1/2 years with the New Jersey Department of Transportation here in the US before retiring. The first half of that time was spent designing roads (I'm a Licensed Professional <Civil Engineer) and the latter half in the department's CADD Development group (providing programming supporting for the engineers and draftsmen in their use of the system). 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 moved to Excel solely to be able to continue offering VB help in the newsgroups. The classic VB newsgroups started to become very slow about five years ago or so, mainly because of the introduction of VB.NET five years before that and Microsoft's de-emphasizing of the classic version of VB as a result. I figured I could apply what I knew about VB programming to the very active Excel macro world, so I started posting in both worlds for awhile (using the time to learn parts of the Excel object model) and eventually changed my MVP discipline from VB to Excel. I started using VBA on Thanksgiving weekend of 2003, when I picked up my first book by JWalk. You remember the exact date? Good memory! I have no idea what the date was when I started in on Excel. 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 Yes, there are some very talented individuals involved in the Excel community and the Internet offers remarkable access to all of them. Man.., I wish I had done that long, long ago! -website UI sucks compared to this! I've read 10s of thousands of postings and only this year did I start using a newsreader. Yes, I like the newsreader access much better than the website access as well. 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 am **not** looking forward to the newsgroup shutdowns either... I find the newsreader approach far more comfortable to use, but I guess I will have to get used to the forum approach as that is where Microsoft has decided to take us all. I wouldn't worry about the losing contact with the "pros" as I expect they will move their volunteering efforts over to the forums when the newsgroups close down. 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. There is always the chance I won't be leaving. Microsoft might renew my MVP status in October... if they do, I expect I would continue in the Excel forums. I only gave the heads up because I don't think the volume of my posting this year will be enough to re-qualify... I had to take nearly a month off to attend to a problem that needed fixing in my house and then there is an upcoming family wedding stealing some of my time as well. Maybe the volume of my postings from now to October will make up for the earlier deficit, but I kind of doubt it. Anyway, we will see what will be. 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. Thank you for the kind words and sentiments... they are much appreciated. -- Rick (MVP - Excel) |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Hi Rick
Sorry I wasn't clear enough. The Sub Terranean macro is the quickest but we need to put the data after some existings data. That's the one that I hope can be modified. Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. Cheers Alan "Rick Rothstein" wrote: See inline... 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. There are some (many?) who would question whether that is a good thing or not.<g 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. Hey, don't think for a moment the solutions I post come to me fully formed. The way my mind works is I see an "outline" (but no details) of the approach I think will work... I have to fight to get everything to work together correctly also. That part, to me, is the fun part of programming. I'm determined to get there, though! Desire is the first and most important step in accomplishing, well, pretty much anything. Since you have it, then I am sure you will succeed. 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 ..$%^!) I am really very sorry to hear this. I hope the progress of this terrible disease goes as well as possible for you. Anyway, between here and the VB forum I've learned so much from all you MVPs I used to be an MVP for the compiled version of the classic version of VB (from VB3 to VB6) before moving over to the Excel world. Perhaps you came across some of my earlier VB postings in your Google searches. ... and so I feel I owe it to the community to give something back. Your participation is most welcome. 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 was a math major in college. 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!) I worked for 32-1/2 years with the New Jersey Department of Transportation here in the US before retiring. The first half of that time was spent designing roads (I'm a Licensed Professional <Civil Engineer) and the latter half in the department's CADD Development group (providing programming supporting for the engineers and draftsmen in their use of the system). 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 moved to Excel solely to be able to continue offering VB help in the newsgroups. The classic VB newsgroups started to become very slow about five years ago or so, mainly because of the introduction of VB.NET five years before that and Microsoft's de-emphasizing of the classic version of VB as a result. I figured I could apply what I knew about VB programming to the very active Excel macro world, so I started posting in both worlds for awhile (using the time to learn parts of the Excel object model) and eventually changed my MVP discipline from VB to Excel. I started using VBA on Thanksgiving weekend of 2003, when I picked up my first book by JWalk. You remember the exact date? Good memory! I have no idea what the date was when I started in on Excel. 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 Yes, there are some very talented individuals involved in the Excel community and the Internet offers remarkable access to all of them. Man.., I wish I had done that long, long ago! -website UI sucks compared to this! I've read 10s of thousands of postings and only this year did I start using a newsreader. Yes, I like the newsreader access much better than the website access as well. 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 am **not** looking forward to the newsgroup shutdowns either... I find the newsreader approach far more comfortable to use, but I guess I will have to get used to the forum approach as that is where Microsoft has decided to take us all. I wouldn't worry about the losing contact with the "pros" as I expect they will move their volunteering efforts over to the forums when the newsgroups close down. 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. There is always the chance I won't be leaving. Microsoft might renew my MVP status in October... if they do, I expect I would continue in the Excel forums. I only gave the heads up because I don't think the volume of my posting this year will be enough to re-qualify... I had to take nearly a month off to attend to a problem that needed fixing in my house and then there is an upcoming family wedding stealing some of my time as well. Maybe the volume of my postings from now to October will make up for the earlier deficit, but I kind of doubt it. Anyway, we will see what will be. 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. Thank you for the kind words and sentiments... they are much appreciated. -- Rick (MVP - Excel) . |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Here is a revision for Sub Terranean for you to try...
Sub Terranean() Dim LastRow As Long LastRow = Sheets("Carryovers").Cells.Find(What:="*", _ SearchOrder:=xlRows, SearchDirection:=xlPrevious, _ LookIn:=xlValues).Row On Error Resume Next Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _ Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow).Copy Sheets("Carryovers").Cells(LastRow + 1, "A") End Sub -- Rick (MVP - Excel) "ajm1949" wrote in message ... Hi Rick Sorry I wasn't clear enough. The Sub Terranean macro is the quickest but we need to put the data after some existings data. That's the one that I hope can be modified. Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. Cheers Alan |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Also need a macro to clear data from the carryover sheet where
column N has a value. This macro will be run before the one to move the data from sales. When you say "clear", I am assuming you mean to delete the entire row so that no gaps appear in your data rows. You can use this macro to delete the rows of data on the Carryovers sheet where Column N has a value placed in it... Sub ject() Sheets("Carryovers").Range("N2:N" & Rows.Count). _ SpecialCells(xlCellTypeConstants).EntireRow.Delete End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a revision for Sub Terranean for you to try... Sub Terranean() Dim LastRow As Long LastRow = Sheets("Carryovers").Cells.Find(What:="*", _ SearchOrder:=xlRows, SearchDirection:=xlPrevious, _ LookIn:=xlValues).Row On Error Resume Next Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _ Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow).Copy Sheets("Carryovers").Cells(LastRow + 1, "A") End Sub -- Rick (MVP - Excel) "ajm1949" wrote in message ... Hi Rick Sorry I wasn't clear enough. The Sub Terranean macro is the quickest but we need to put the data after some existings data. That's the one that I hope can be modified. Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. Cheers Alan |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Rick Rothstein pretended :
See inline... 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. There are some (many?) who would question whether that is a good thing or not.<g I would not be one of those!<g 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. Hey, don't think for a moment the solutions I post come to me fully formed. The way my mind works is I see an "outline" (but no details) of the approach I think will work... I have to fight to get everything to work together correctly also. That part, to me, is the fun part of programming. Isn't that the truth! I'm determined to get there, though! Desire is the first and most important step in accomplishing, well, pretty much anything. Since you have it, then I am sure you will succeed. Well, the ALS know-it-alls can't figure out why I'm still here. They claim my passion for my work is what keeps me going. (I was diagnosed Mar '95 after 2+ years of tests) 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 ..$%^!) I am really very sorry to hear this. I hope the progress of this terrible disease goes as well as possible for you. Thanks for the sentiment! What would make me happy is if its progress doesn't go so well!<g That way I'll live longer!<bg (I know what you meant so please forgive my sense of humor) Anyway, between here and the VB forum I've learned so much from all you MVPs I used to be an MVP for the compiled version of the classic version of VB (from VB3 to VB6) before moving over to the Excel world. Perhaps you came across some of my earlier VB postings in your Google searches. Yes, your name is familiar from both NGs. ... and so I feel I owe it to the community to give something back. Your participation is most welcome. Thanks! I'll do my best to make it worthy of membership! 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 was a math major in college. I was a math failure until I started having to use it every day whether I liked to or not. 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!) I worked for 32-1/2 years with the New Jersey Department of Transportation here in the US before retiring. The first half of that time was spent designing roads (I'm a Licensed Professional <Civil Engineer) and the latter half in the department's CADD Development group (providing programming supporting for the engineers and draftsmen in their use of the system). That's interesting. When I first started programming in VBA, it was while working for one of the major engine rebuilding equipment manufacturers, designing machine fixtures and specialty tooling. I've been using CADD software since the late 80's. I still do some free lance stuff. During those years I got a chance to work with many programs, some mickey mouse but most fairly serious (AutoCad, ME10/ME30 (Unix), CadKey, SolidWorks, ..to name a few) I now just use SolidWorks. CNC machining programming was all I was familiar with before getting into VBA. 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 moved to Excel solely to be able to continue offering VB help in the newsgroups. The classic VB newsgroups started to become very slow about five years ago or so, mainly because of the introduction of VB.NET five years before that and Microsoft's de-emphasizing of the classic version of VB as a result. I figured I could apply what I knew about VB programming to the very active Excel macro world, so I started posting in both worlds for awhile (using the time to learn parts of the Excel object model) and eventually changed my MVP discipline from VB to Excel. Well, I'm sure glad you're here! I started using VBA on Thanksgiving weekend of 2003, when I picked up my first book by JWalk. You remember the exact date? Good memory! I have no idea what the date was when I started in on Excel. I remember because it seems like a week ago or so. Hey.., I'm a newbie, and so it goes that I should still remember! 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 Yes, there are some very talented individuals involved in the Excel community and the Internet offers remarkable access to all of them. Man.., I wish I had done that long, long ago! -website UI sucks compared to this! I've read 10s of thousands of postings and only this year did I start using a newsreader. Yes, I like the newsreader access much better than the website access as well. 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 am **not** looking forward to the newsgroup shutdowns either... I find the newsreader approach far more comfortable to use, but I guess I will have to get used to the forum approach as that is where Microsoft has decided to take us all. I wouldn't worry about the losing contact with the "pros" as I expect they will move their volunteering efforts over to the forums when the newsgroups close down. 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. There is always the chance I won't be leaving. Microsoft might renew my MVP status in October... if they do, I expect I would continue in the Excel forums. I only gave the heads up because I don't think the volume of my posting this year will be enough to re-qualify... I had to take nearly a month off to attend to a problem that needed fixing in my house and then there is an upcoming family wedding stealing some of my time as well. Maybe the volume of my postings from now to October will make up for the earlier deficit, but I kind of doubt it. Anyway, we will see what will be. 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. Thank you for the kind words and sentiments... they are much appreciated. Regards, Garry |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Damn, I forgot the On Error statement (needed in case all of Column N is
blank)... Sub ject() On Error Resume Next Sheets("Carryovers").Range("N2:N" & Rows.Count). _ SpecialCells(xlCellTypeConstants).EntireRow.Delete End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. When you say "clear", I am assuming you mean to delete the entire row so that no gaps appear in your data rows. You can use this macro to delete the rows of data on the Carryovers sheet where Column N has a value placed in it... Sub ject() Sheets("Carryovers").Range("N2:N" & Rows.Count). _ SpecialCells(xlCellTypeConstants).EntireRow.Delete End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a revision for Sub Terranean for you to try... Sub Terranean() Dim LastRow As Long LastRow = Sheets("Carryovers").Cells.Find(What:="*", _ SearchOrder:=xlRows, SearchDirection:=xlPrevious, _ LookIn:=xlValues).Row On Error Resume Next Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _ Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow).Copy Sheets("Carryovers").Cells(LastRow + 1, "A") End Sub -- Rick (MVP - Excel) "ajm1949" wrote in message ... Hi Rick Sorry I wasn't clear enough. The Sub Terranean macro is the quickest but we need to put the data after some existings data. That's the one that I hope can be modified. Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. Cheers Alan |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Hi Rick
Sorry to say that the new code samples seem to be missing something as they don't work. Revised Sub terranean come up with error 9 subscript out of range with the lastrow=etc highlighted as the culprit. Sub ject macro does nothing at all I am using excel 2003 with all the latest service packs an updates Cheers Alan "Rick Rothstein" wrote: Damn, I forgot the On Error statement (needed in case all of Column N is blank)... Sub ject() On Error Resume Next Sheets("Carryovers").Range("N2:N" & Rows.Count). _ SpecialCells(xlCellTypeConstants).EntireRow.Delete End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. When you say "clear", I am assuming you mean to delete the entire row so that no gaps appear in your data rows. You can use this macro to delete the rows of data on the Carryovers sheet where Column N has a value placed in it... Sub ject() Sheets("Carryovers").Range("N2:N" & Rows.Count). _ SpecialCells(xlCellTypeConstants).EntireRow.Delete End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a revision for Sub Terranean for you to try... Sub Terranean() Dim LastRow As Long LastRow = Sheets("Carryovers").Cells.Find(What:="*", _ SearchOrder:=xlRows, SearchDirection:=xlPrevious, _ LookIn:=xlValues).Row On Error Resume Next Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _ Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow).Copy Sheets("Carryovers").Cells(LastRow + 1, "A") End Sub -- Rick (MVP - Excel) "ajm1949" wrote in message ... Hi Rick Sorry I wasn't clear enough. The Sub Terranean macro is the quickest but we need to put the data after some existings data. That's the one that I hope can be modified. Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. Cheers Alan . |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
I'm using XL2003 as well and both of those macros worked on the sample data
I made up. Is there any chance you can send me a copy of your workbook so I can see what is going on directly (it would be okay for you to replace real data with fake data, but I'm interested in the values in Column N on both sheets... in particular, whether they are typed in values or formulas? If so, set it up so that its in the condition just prior to needing the macros run. You will need to remove the NO.SPAM stuff from my email address. -- Rick (MVP - Excel) "ajm1949" wrote in message ... Hi Rick Sorry to say that the new code samples seem to be missing something as they don't work. Revised Sub terranean come up with error 9 subscript out of range with the lastrow=etc highlighted as the culprit. Sub ject macro does nothing at all I am using excel 2003 with all the latest service packs an updates Cheers Alan "Rick Rothstein" wrote: Damn, I forgot the On Error statement (needed in case all of Column N is blank)... Sub ject() On Error Resume Next Sheets("Carryovers").Range("N2:N" & Rows.Count). _ SpecialCells(xlCellTypeConstants).EntireRow.Delete End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. When you say "clear", I am assuming you mean to delete the entire row so that no gaps appear in your data rows. You can use this macro to delete the rows of data on the Carryovers sheet where Column N has a value placed in it... Sub ject() Sheets("Carryovers").Range("N2:N" & Rows.Count). _ SpecialCells(xlCellTypeConstants).EntireRow.Delete End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a revision for Sub Terranean for you to try... Sub Terranean() Dim LastRow As Long LastRow = Sheets("Carryovers").Cells.Find(What:="*", _ SearchOrder:=xlRows, SearchDirection:=xlPrevious, _ LookIn:=xlValues).Row On Error Resume Next Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _ Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow).Copy Sheets("Carryovers").Cells(LastRow + 1, "A") End Sub -- Rick (MVP - Excel) "ajm1949" wrote in message ... Hi Rick Sorry I wasn't clear enough. The Sub Terranean macro is the quickest but we need to put the data after some existings data. That's the one that I hope can be modified. Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. Cheers Alan . |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Hi Rick
Have been doing some more research and came up with this modofication of you first code sample Sub terranean1() Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _ Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow).Copy Sheets("Carry overs").Cells(Rows.Count, 1).End(xlUp)(2) End Sub This seems to work and runs quickly. Can you see any problems with this? Still doing some more homework on the code to clean up the Carry Overs sheet Thanks again for your help Cheers Alan "ajm1949" wrote: Hi Rick Sorry to say that the new code samples seem to be missing something as they don't work. Revised Sub terranean come up with error 9 subscript out of range with the lastrow=etc highlighted as the culprit. Sub ject macro does nothing at all I am using excel 2003 with all the latest service packs an updates Cheers Alan "Rick Rothstein" wrote: Damn, I forgot the On Error statement (needed in case all of Column N is blank)... Sub ject() On Error Resume Next Sheets("Carryovers").Range("N2:N" & Rows.Count). _ SpecialCells(xlCellTypeConstants).EntireRow.Delete End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. When you say "clear", I am assuming you mean to delete the entire row so that no gaps appear in your data rows. You can use this macro to delete the rows of data on the Carryovers sheet where Column N has a value placed in it... Sub ject() Sheets("Carryovers").Range("N2:N" & Rows.Count). _ SpecialCells(xlCellTypeConstants).EntireRow.Delete End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a revision for Sub Terranean for you to try... Sub Terranean() Dim LastRow As Long LastRow = Sheets("Carryovers").Cells.Find(What:="*", _ SearchOrder:=xlRows, SearchDirection:=xlPrevious, _ LookIn:=xlValues).Row On Error Resume Next Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _ Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow).Copy Sheets("Carryovers").Cells(LastRow + 1, "A") End Sub -- Rick (MVP - Excel) "ajm1949" wrote in message ... Hi Rick Sorry I wasn't clear enough. The Sub Terranean macro is the quickest but we need to put the data after some existings data. That's the one that I hope can be modified. Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. Cheers Alan . |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
As long as no other column will have more rows filled in than Column A, that
should work fine. The code I gave you, which should work, finds the largest row number no matter what column that row is in and uses that to set the copy position. -- Rick (MVP - Excel) "ajm1949" wrote in message ... Hi Rick Have been doing some more research and came up with this modofication of you first code sample Sub terranean1() Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _ Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow).Copy Sheets("Carry overs").Cells(Rows.Count, 1).End(xlUp)(2) End Sub This seems to work and runs quickly. Can you see any problems with this? Still doing some more homework on the code to clean up the Carry Overs sheet Thanks again for your help Cheers Alan "ajm1949" wrote: Hi Rick Sorry to say that the new code samples seem to be missing something as they don't work. Revised Sub terranean come up with error 9 subscript out of range with the lastrow=etc highlighted as the culprit. Sub ject macro does nothing at all I am using excel 2003 with all the latest service packs an updates Cheers Alan "Rick Rothstein" wrote: Damn, I forgot the On Error statement (needed in case all of Column N is blank)... Sub ject() On Error Resume Next Sheets("Carryovers").Range("N2:N" & Rows.Count). _ SpecialCells(xlCellTypeConstants).EntireRow.Delete End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. When you say "clear", I am assuming you mean to delete the entire row so that no gaps appear in your data rows. You can use this macro to delete the rows of data on the Carryovers sheet where Column N has a value placed in it... Sub ject() Sheets("Carryovers").Range("N2:N" & Rows.Count). _ SpecialCells(xlCellTypeConstants).EntireRow.Delete End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a revision for Sub Terranean for you to try... Sub Terranean() Dim LastRow As Long LastRow = Sheets("Carryovers").Cells.Find(What:="*", _ SearchOrder:=xlRows, SearchDirection:=xlPrevious, _ LookIn:=xlValues).Row On Error Resume Next Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _ Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow).Copy Sheets("Carryovers").Cells(LastRow + 1, "A") End Sub -- Rick (MVP - Excel) "ajm1949" wrote in message ... Hi Rick Sorry I wasn't clear enough. The Sub Terranean macro is the quickest but we need to put the data after some existings data. That's the one that I hope can be modified. Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. Cheers Alan . |
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
Thanks Rick
Email is on the way Alan "Rick Rothstein" wrote: I'm using XL2003 as well and both of those macros worked on the sample data I made up. Is there any chance you can send me a copy of your workbook so I can see what is going on directly (it would be okay for you to replace real data with fake data, but I'm interested in the values in Column N on both sheets... in particular, whether they are typed in values or formulas? If so, set it up so that its in the condition just prior to needing the macros run. You will need to remove the NO.SPAM stuff from my email address. -- Rick (MVP - Excel) "ajm1949" wrote in message ... Hi Rick Sorry to say that the new code samples seem to be missing something as they don't work. Revised Sub terranean come up with error 9 subscript out of range with the lastrow=etc highlighted as the culprit. Sub ject macro does nothing at all I am using excel 2003 with all the latest service packs an updates Cheers Alan "Rick Rothstein" wrote: Damn, I forgot the On Error statement (needed in case all of Column N is blank)... Sub ject() On Error Resume Next Sheets("Carryovers").Range("N2:N" & Rows.Count). _ SpecialCells(xlCellTypeConstants).EntireRow.Delete End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. When you say "clear", I am assuming you mean to delete the entire row so that no gaps appear in your data rows. You can use this macro to delete the rows of data on the Carryovers sheet where Column N has a value placed in it... Sub ject() Sheets("Carryovers").Range("N2:N" & Rows.Count). _ SpecialCells(xlCellTypeConstants).EntireRow.Delete End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a revision for Sub Terranean for you to try... Sub Terranean() Dim LastRow As Long LastRow = Sheets("Carryovers").Cells.Find(What:="*", _ SearchOrder:=xlRows, SearchDirection:=xlPrevious, _ LookIn:=xlValues).Row On Error Resume Next Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _ Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow).Copy Sheets("Carryovers").Cells(LastRow + 1, "A") End Sub -- Rick (MVP - Excel) "ajm1949" wrote in message ... Hi Rick Sorry I wasn't clear enough. The Sub Terranean macro is the quickest but we need to put the data after some existings data. That's the one that I hope can be modified. Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. Cheers Alan . . |
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range if column N is empty
I just sent the answer back to you. The problem appears to be in the name of
the carry overs sheet... since you said the name was "Carryovers" (one word) in your original message, I used that; however, I see on the workbook you sent me that the sheet is actually named "Carry overs" (two words)... make that change in my posted code and the code should work fine for you. -- Rick (MVP - Excel) "ajm1949" wrote in message ... Thanks Rick Email is on the way Alan "Rick Rothstein" wrote: I'm using XL2003 as well and both of those macros worked on the sample data I made up. Is there any chance you can send me a copy of your workbook so I can see what is going on directly (it would be okay for you to replace real data with fake data, but I'm interested in the values in Column N on both sheets... in particular, whether they are typed in values or formulas? If so, set it up so that its in the condition just prior to needing the macros run. You will need to remove the NO.SPAM stuff from my email address. -- Rick (MVP - Excel) "ajm1949" wrote in message ... Hi Rick Sorry to say that the new code samples seem to be missing something as they don't work. Revised Sub terranean come up with error 9 subscript out of range with the lastrow=etc highlighted as the culprit. Sub ject macro does nothing at all I am using excel 2003 with all the latest service packs an updates Cheers Alan "Rick Rothstein" wrote: Damn, I forgot the On Error statement (needed in case all of Column N is blank)... Sub ject() On Error Resume Next Sheets("Carryovers").Range("N2:N" & Rows.Count). _ SpecialCells(xlCellTypeConstants).EntireRow.Delete End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. When you say "clear", I am assuming you mean to delete the entire row so that no gaps appear in your data rows. You can use this macro to delete the rows of data on the Carryovers sheet where Column N has a value placed in it... Sub ject() Sheets("Carryovers").Range("N2:N" & Rows.Count). _ SpecialCells(xlCellTypeConstants).EntireRow.Delete End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a revision for Sub Terranean for you to try... Sub Terranean() Dim LastRow As Long LastRow = Sheets("Carryovers").Cells.Find(What:="*", _ SearchOrder:=xlRows, SearchDirection:=xlPrevious, _ LookIn:=xlValues).Row On Error Resume Next Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _ Columns("N").SpecialCells(xlCellTypeBlanks). _ EntireRow).Copy Sheets("Carryovers").Cells(LastRow + 1, "A") End Sub -- Rick (MVP - Excel) "ajm1949" wrote in message ... Hi Rick Sorry I wasn't clear enough. The Sub Terranean macro is the quickest but we need to put the data after some existings data. That's the one that I hope can be modified. Also need a macro to clear data from the carryover sheet where column N has a value. This macro will be run before the one to move the data from sales. Cheers Alan . . |
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) |