|
Clearing contents from a range
Try this macro (change the worksheet reference and starting row Const as
required).... Sub ClearExcessData() Dim X As Long Dim LastRow As Long Const StartRow As Long = 2 With Worksheets("Sheet3") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = StartRow To LastRow If .Cells(X, "A").Offset(1).Value < "" Then .Cells(X, "D").Resize(1, .Columns.Count - 4).Clear End If Next End With End Sub -- Rick (MVP - Excel) "G. Yamada" wrote in message ... Hello, I am completely new to Excel VBA and I have a task that can only be done using it. I need to format a group of orders to upload them. Essentially I start with: 78693624286 1 9.71 2793888 .... 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 2792280 ... 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 2791190 ... 02454305295 1 21.86 2791190 ... 78693614475 1 7.25 2791190 ... 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 .. What I want to end up with is: 78678693286 1 9.71 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 02454305295 1 21.86 78693614475 1 7.25 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 ... The orders are separated by an empty row (which is already done for me) according to the order numbers in the D column (numbers starting with 279...). Each order may have 1 or more items designated by the item number in the first column. As you can see, the first three orders (groups) have more than one item in the order. These are what have been causing an issue for me. I need to clear the information from the D column onwards (including D) on the item lines (rows) which are part of the same order EXCEPT for the last line in the order as shown above. I would sincerely appreciate any help. |
Clearing contents from a range
Try this...
Sub ClearStuff() Dim rngAll As Range Dim rngArea As Range On Error Resume Next Set rngAll = Range("D:D").SpecialCells(xlCellTypeConstants) If Not rngAll Is Nothing Then For Each rngArea In rngAll.Areas If rngArea.Cells.Count 1 Then Range(rngArea.Cells(1), _ rngArea.Cells(rngArea.Count - 1).Offset(0, 1)).ClearContents End If Next rngArea End If End Sub -- HTH... Jim Thomlinson "G. Yamada" wrote: Hello, I am completely new to Excel VBA and I have a task that can only be done using it. I need to format a group of orders to upload them. Essentially I start with: 78693624286 1 9.71 2793888 .... 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 2792280 ... 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 2791190 ... 02454305295 1 21.86 2791190 ... 78693614475 1 7.25 2791190 ... 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 .. What I want to end up with is: 78678693286 1 9.71 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 02454305295 1 21.86 78693614475 1 7.25 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 ... The orders are separated by an empty row (which is already done for me) according to the order numbers in the D column (numbers starting with 279...). Each order may have 1 or more items designated by the item number in the first column. As you can see, the first three orders (groups) have more than one item in the order. These are what have been causing an issue for me. I need to clear the information from the D column onwards (including D) on the item lines (rows) which are part of the same order EXCEPT for the last line in the order as shown above. I would sincerely appreciate any help. |
Clearing contents from a range
Hello,
Thank you for you reply. This almost does the trick for me. I need to have all the columns in the row (to be cleared) at least up to column Q to be cleared. So far, the program you provided only clears columns D and E. If you could revise the program to reflect this change, I would sincerly appreciate it. Thank you so much for providing this. I have put many hours into it already and should have turned to the forum much earlier. P.S. Is there a fast and efficient way to learn VBA for Excel? "Jim Thomlinson" wrote: Try this... Sub ClearStuff() Dim rngAll As Range Dim rngArea As Range On Error Resume Next Set rngAll = Range("D:D").SpecialCells(xlCellTypeConstants) If Not rngAll Is Nothing Then For Each rngArea In rngAll.Areas If rngArea.Cells.Count 1 Then Range(rngArea.Cells(1), _ rngArea.Cells(rngArea.Count - 1).Offset(0, 1)).ClearContents End If Next rngArea End If End Sub -- HTH... Jim Thomlinson "G. Yamada" wrote: Hello, I am completely new to Excel VBA and I have a task that can only be done using it. I need to format a group of orders to upload them. Essentially I start with: 78693624286 1 9.71 2793888 .... 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 2792280 ... 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 2791190 ... 02454305295 1 21.86 2791190 ... 78693614475 1 7.25 2791190 ... 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 .. What I want to end up with is: 78678693286 1 9.71 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 02454305295 1 21.86 78693614475 1 7.25 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 ... The orders are separated by an empty row (which is already done for me) according to the order numbers in the D column (numbers starting with 279...). Each order may have 1 or more items designated by the item number in the first column. As you can see, the first three orders (groups) have more than one item in the order. These are what have been causing an issue for me. I need to clear the information from the D column onwards (including D) on the item lines (rows) which are part of the same order EXCEPT for the last line in the order as shown above. I would sincerely appreciate any help. |
Clearing contents from a range
Hi
Just change Jim's offset from 1 to 13 rngArea.Cells(rngArea.Count - 1).Offset(0, 13)).ClearContents -- Regards Roger Govier "G. Yamada" wrote in message ... Hello, Thank you for you reply. This almost does the trick for me. I need to have all the columns in the row (to be cleared) at least up to column Q to be cleared. So far, the program you provided only clears columns D and E. If you could revise the program to reflect this change, I would sincerly appreciate it. Thank you so much for providing this. I have put many hours into it already and should have turned to the forum much earlier. P.S. Is there a fast and efficient way to learn VBA for Excel? "Jim Thomlinson" wrote: Try this... Sub ClearStuff() Dim rngAll As Range Dim rngArea As Range On Error Resume Next Set rngAll = Range("D:D").SpecialCells(xlCellTypeConstants) If Not rngAll Is Nothing Then For Each rngArea In rngAll.Areas If rngArea.Cells.Count 1 Then Range(rngArea.Cells(1), _ rngArea.Cells(rngArea.Count - 1).Offset(0, 1)).ClearContents End If Next rngArea End If End Sub -- HTH... Jim Thomlinson "G. Yamada" wrote: Hello, I am completely new to Excel VBA and I have a task that can only be done using it. I need to format a group of orders to upload them. Essentially I start with: 78693624286 1 9.71 2793888 .... 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 2792280 ... 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 2791190 ... 02454305295 1 21.86 2791190 ... 78693614475 1 7.25 2791190 ... 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 .. What I want to end up with is: 78678693286 1 9.71 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 02454305295 1 21.86 78693614475 1 7.25 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 ... The orders are separated by an empty row (which is already done for me) according to the order numbers in the D column (numbers starting with 279...). Each order may have 1 or more items designated by the item number in the first column. As you can see, the first three orders (groups) have more than one item in the order. These are what have been causing an issue for me. I need to clear the information from the D column onwards (including D) on the item lines (rows) which are part of the same order EXCEPT for the last line in the order as shown above. I would sincerely appreciate any help. |
Clearing contents from a range
Not that I am knocking Jim's code because (with the change Roger suggested)
it should work fine; but I was wondering, did you even try my posted code? Just so you know, it works too. -- Rick (MVP - Excel) "G. Yamada" wrote in message ... Hello, Thank you for you reply. This almost does the trick for me. I need to have all the columns in the row (to be cleared) at least up to column Q to be cleared. So far, the program you provided only clears columns D and E. If you could revise the program to reflect this change, I would sincerly appreciate it. Thank you so much for providing this. I have put many hours into it already and should have turned to the forum much earlier. P.S. Is there a fast and efficient way to learn VBA for Excel? "Jim Thomlinson" wrote: Try this... Sub ClearStuff() Dim rngAll As Range Dim rngArea As Range On Error Resume Next Set rngAll = Range("D:D").SpecialCells(xlCellTypeConstants) If Not rngAll Is Nothing Then For Each rngArea In rngAll.Areas If rngArea.Cells.Count 1 Then Range(rngArea.Cells(1), _ rngArea.Cells(rngArea.Count - 1).Offset(0, 1)).ClearContents End If Next rngArea End If End Sub -- HTH... Jim Thomlinson "G. Yamada" wrote: Hello, I am completely new to Excel VBA and I have a task that can only be done using it. I need to format a group of orders to upload them. Essentially I start with: 78693624286 1 9.71 2793888 .... 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 2792280 ... 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 2791190 ... 02454305295 1 21.86 2791190 ... 78693614475 1 7.25 2791190 ... 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 .. What I want to end up with is: 78678693286 1 9.71 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 02454305295 1 21.86 78693614475 1 7.25 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 ... The orders are separated by an empty row (which is already done for me) according to the order numbers in the D column (numbers starting with 279...). Each order may have 1 or more items designated by the item number in the first column. As you can see, the first three orders (groups) have more than one item in the order. These are what have been causing an issue for me. I need to clear the information from the D column onwards (including D) on the item lines (rows) which are part of the same order EXCEPT for the last line in the order as shown above. I would sincerely appreciate any help. |
Clearing contents from a range
I kinda liked your code... At first blush I thought it was going to take more
code than that to set start and end rows as you did so I used the areas. I thought I was being kinda slick and avoiding a lot of headaches. You proved me wrong. To G. Yamada - Is there a quick way to learn VBA... In short no. VBA is a language like any other language. The only way to be good at it is to devote time and effort towards it. Excel VBA for dummies is a great place to start. Check out this link... http://spreadsheetpage.com/index.php/books/ -- HTH... Jim Thomlinson "Rick Rothstein" wrote: Not that I am knocking Jim's code because (with the change Roger suggested) it should work fine; but I was wondering, did you even try my posted code? Just so you know, it works too. -- Rick (MVP - Excel) "G. Yamada" wrote in message ... Hello, Thank you for you reply. This almost does the trick for me. I need to have all the columns in the row (to be cleared) at least up to column Q to be cleared. So far, the program you provided only clears columns D and E. If you could revise the program to reflect this change, I would sincerly appreciate it. Thank you so much for providing this. I have put many hours into it already and should have turned to the forum much earlier. P.S. Is there a fast and efficient way to learn VBA for Excel? "Jim Thomlinson" wrote: Try this... Sub ClearStuff() Dim rngAll As Range Dim rngArea As Range On Error Resume Next Set rngAll = Range("D:D").SpecialCells(xlCellTypeConstants) If Not rngAll Is Nothing Then For Each rngArea In rngAll.Areas If rngArea.Cells.Count 1 Then Range(rngArea.Cells(1), _ rngArea.Cells(rngArea.Count - 1).Offset(0, 1)).ClearContents End If Next rngArea End If End Sub -- HTH... Jim Thomlinson "G. Yamada" wrote: Hello, I am completely new to Excel VBA and I have a task that can only be done using it. I need to format a group of orders to upload them. Essentially I start with: 78693624286 1 9.71 2793888 .... 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 2792280 ... 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 2791190 ... 02454305295 1 21.86 2791190 ... 78693614475 1 7.25 2791190 ... 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 .. What I want to end up with is: 78678693286 1 9.71 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 02454305295 1 21.86 78693614475 1 7.25 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 ... The orders are separated by an empty row (which is already done for me) according to the order numbers in the D column (numbers starting with 279...). Each order may have 1 or more items designated by the item number in the first column. As you can see, the first three orders (groups) have more than one item in the order. These are what have been causing an issue for me. I need to clear the information from the D column onwards (including D) on the item lines (rows) which are part of the same order EXCEPT for the last line in the order as shown above. I would sincerely appreciate any help. |
Clearing contents from a range
See inline comments..
I kinda liked your code... Thanks! At first blush I thought it was going to take more code than that to set start and end rows as you did so I used the areas. I thought I was being kinda slick and avoiding a lot of headaches. You proved me wrong. That's what I have always liked about programming in general (and this includes Excel formula development as well)... there are usually more than one way to code a solution and each tends to be interesting in their own right. Sometimes an idea you see someone use in one of them may becomes a central method you choose for a similar, but different problem later on. To G. Yamada - Is there a quick way to learn VBA... In short no. VBA is a language like any other language. The only way to be good at it is to devote time and effort towards it. Excel VBA for dummies is a great place to start. Check out this link... I have found the "For Dummies" series of books to be a great introduction to the topics they cover so, yes G. Yamada, that would be a great place to start. You might also find the brief tutorial at this link to be helpful as well. -- Rick (MVP - Excel) |
Clearing contents from a range
Hi Rick,
I have found the "For Dummies" series of books to be a great introduction to the topics they cover so, yes G. Yamada, that would be a great place to start. You might also find the brief tutorial at this link to be helpful as well. My browser didn't show a link. Could you please repost it? Thanks, Dan |
Clearing contents from a range
Actually, it apparently never got pasted into my original message. Here it
is... http://www.anthony-vba.kefra.com/ -- Rick (MVP - Excel) "dan dungan" wrote in message ... Hi Rick, I have found the "For Dummies" series of books to be a great introduction to the topics they cover so, yes G. Yamada, that would be a great place to start. You might also find the brief tutorial at this link to be helpful as well. My browser didn't show a link. Could you please repost it? Thanks, Dan |
Clearing contents from a range
Thanks Rick.
|
Clearing contents from a range
Hi Rick,
I did try your code, but it appeared that it kept stopping where the worksheet name was indicated. I changed this according to what worksheet I was in, but it continued to produce an error. Thank you for the help nonetheless. "Rick Rothstein" wrote: Not that I am knocking Jim's code because (with the change Roger suggested) it should work fine; but I was wondering, did you even try my posted code? Just so you know, it works too. -- Rick (MVP - Excel) "G. Yamada" wrote in message ... Hello, Thank you for you reply. This almost does the trick for me. I need to have all the columns in the row (to be cleared) at least up to column Q to be cleared. So far, the program you provided only clears columns D and E. If you could revise the program to reflect this change, I would sincerly appreciate it. Thank you so much for providing this. I have put many hours into it already and should have turned to the forum much earlier. P.S. Is there a fast and efficient way to learn VBA for Excel? "Jim Thomlinson" wrote: Try this... Sub ClearStuff() Dim rngAll As Range Dim rngArea As Range On Error Resume Next Set rngAll = Range("D:D").SpecialCells(xlCellTypeConstants) If Not rngAll Is Nothing Then For Each rngArea In rngAll.Areas If rngArea.Cells.Count 1 Then Range(rngArea.Cells(1), _ rngArea.Cells(rngArea.Count - 1).Offset(0, 1)).ClearContents End If Next rngArea End If End Sub -- HTH... Jim Thomlinson "G. Yamada" wrote: Hello, I am completely new to Excel VBA and I have a task that can only be done using it. I need to format a group of orders to upload them. Essentially I start with: 78693624286 1 9.71 2793888 .... 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 2792280 ... 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 2791190 ... 02454305295 1 21.86 2791190 ... 78693614475 1 7.25 2791190 ... 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 .. What I want to end up with is: 78678693286 1 9.71 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 02454305295 1 21.86 78693614475 1 7.25 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 ... The orders are separated by an empty row (which is already done for me) according to the order numbers in the D column (numbers starting with 279...). Each order may have 1 or more items designated by the item number in the first column. As you can see, the first three orders (groups) have more than one item in the order. These are what have been causing an issue for me. I need to clear the information from the D column onwards (including D) on the item lines (rows) which are part of the same order EXCEPT for the last line in the order as shown above. I would sincerely appreciate any help. |
Clearing contents from a range
Hi Jim,
Thanks again for your help. The code works fine now with the minor adjustment Roger suggested. "Jim Thomlinson" wrote: I kinda liked your code... At first blush I thought it was going to take more code than that to set start and end rows as you did so I used the areas. I thought I was being kinda slick and avoiding a lot of headaches. You proved me wrong. To G. Yamada - Is there a quick way to learn VBA... In short no. VBA is a language like any other language. The only way to be good at it is to devote time and effort towards it. Excel VBA for dummies is a great place to start. Check out this link... http://spreadsheetpage.com/index.php/books/ -- HTH... Jim Thomlinson "Rick Rothstein" wrote: Not that I am knocking Jim's code because (with the change Roger suggested) it should work fine; but I was wondering, did you even try my posted code? Just so you know, it works too. -- Rick (MVP - Excel) "G. Yamada" wrote in message ... Hello, Thank you for you reply. This almost does the trick for me. I need to have all the columns in the row (to be cleared) at least up to column Q to be cleared. So far, the program you provided only clears columns D and E. If you could revise the program to reflect this change, I would sincerly appreciate it. Thank you so much for providing this. I have put many hours into it already and should have turned to the forum much earlier. P.S. Is there a fast and efficient way to learn VBA for Excel? "Jim Thomlinson" wrote: Try this... Sub ClearStuff() Dim rngAll As Range Dim rngArea As Range On Error Resume Next Set rngAll = Range("D:D").SpecialCells(xlCellTypeConstants) If Not rngAll Is Nothing Then For Each rngArea In rngAll.Areas If rngArea.Cells.Count 1 Then Range(rngArea.Cells(1), _ rngArea.Cells(rngArea.Count - 1).Offset(0, 1)).ClearContents End If Next rngArea End If End Sub -- HTH... Jim Thomlinson "G. Yamada" wrote: Hello, I am completely new to Excel VBA and I have a task that can only be done using it. I need to format a group of orders to upload them. Essentially I start with: 78693624286 1 9.71 2793888 .... 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 2792280 ... 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 2791190 ... 02454305295 1 21.86 2791190 ... 78693614475 1 7.25 2791190 ... 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 .. What I want to end up with is: 78678693286 1 9.71 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 02454305295 1 21.86 78693614475 1 7.25 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 ... The orders are separated by an empty row (which is already done for me) according to the order numbers in the D column (numbers starting with 279...). Each order may have 1 or more items designated by the item number in the first column. As you can see, the first three orders (groups) have more than one item in the order. These are what have been causing an issue for me. I need to clear the information from the D column onwards (including D) on the item lines (rows) which are part of the same order EXCEPT for the last line in the order as shown above. I would sincerely appreciate any help. |
Clearing contents from a range
That is strange. I tested the code before I posted it and it worked fine for
me. Based on Jim's response to one of my other posts in this thread, I gather my code worked fine for him also. Out of curiosity, what error message did you get when you ran my code (after changing the worksheet name to one of your own worksheet names)? -- Rick (MVP - Excel) "G. Yamada" wrote in message ... Hi Rick, I did try your code, but it appeared that it kept stopping where the worksheet name was indicated. I changed this according to what worksheet I was in, but it continued to produce an error. Thank you for the help nonetheless. "Rick Rothstein" wrote: Not that I am knocking Jim's code because (with the change Roger suggested) it should work fine; but I was wondering, did you even try my posted code? Just so you know, it works too. -- Rick (MVP - Excel) "G. Yamada" wrote in message ... Hello, Thank you for you reply. This almost does the trick for me. I need to have all the columns in the row (to be cleared) at least up to column Q to be cleared. So far, the program you provided only clears columns D and E. If you could revise the program to reflect this change, I would sincerly appreciate it. Thank you so much for providing this. I have put many hours into it already and should have turned to the forum much earlier. P.S. Is there a fast and efficient way to learn VBA for Excel? "Jim Thomlinson" wrote: Try this... Sub ClearStuff() Dim rngAll As Range Dim rngArea As Range On Error Resume Next Set rngAll = Range("D:D").SpecialCells(xlCellTypeConstants) If Not rngAll Is Nothing Then For Each rngArea In rngAll.Areas If rngArea.Cells.Count 1 Then Range(rngArea.Cells(1), _ rngArea.Cells(rngArea.Count - 1).Offset(0, 1)).ClearContents End If Next rngArea End If End Sub -- HTH... Jim Thomlinson "G. Yamada" wrote: Hello, I am completely new to Excel VBA and I have a task that can only be done using it. I need to format a group of orders to upload them. Essentially I start with: 78693624286 1 9.71 2793888 .... 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 2792280 ... 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 2791190 ... 02454305295 1 21.86 2791190 ... 78693614475 1 7.25 2791190 ... 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 .. What I want to end up with is: 78678693286 1 9.71 02761688770 1 2.99 2793888 ... 02454352960 1 8.45 02454352959 1 8.45 2792280 ... 78693675842 1 8.65 02454305295 1 21.86 78693614475 1 7.25 04339614171 1 9.77 2791190 ... 08536513892 1 3.99 2791055 ... The orders are separated by an empty row (which is already done for me) according to the order numbers in the D column (numbers starting with 279...). Each order may have 1 or more items designated by the item number in the first column. As you can see, the first three orders (groups) have more than one item in the order. These are what have been causing an issue for me. I need to clear the information from the D column onwards (including D) on the item lines (rows) which are part of the same order EXCEPT for the last line in the order as shown above. I would sincerely appreciate any help. |
| All times are GMT +1. The time now is 06:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com