| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows Between Border Lines
I will try to sum all this up quickly so I do not ramble on. I am making a
price quoting worksheet. In Cell B6, I have a "Reference Number", that represents a product, in a cell and 3 rows down I have another cell with the word "Remove" in it. In Range("C6:C20") I have the products description. The products description can range from 4 to 15 rows. The reference number cell is always in Column B and next to the first line of the product description and the remove cell is always 3 rows below the reference number. Once the data is added to the worksheet, a thin line border is inserted around the product description range. I want the user to be able to double click the cell with "Remove" in it next to the product description range and then a macro will delete the entire rows the range is located. I have the double click event already. I just need the loop to delete the range between the border. Any help pointing me in the right direction would greatly be appreciated!! THanks!! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Rows Between Border Lines
You don't need a loop, just 2 lines off code
LastRow = Range("C6").End(xlDown).Row Rows("6:" & LastRow).Delete "RyanH" wrote: I will try to sum all this up quickly so I do not ramble on. I am making a price quoting worksheet. In Cell B6, I have a "Reference Number", that represents a product, in a cell and 3 rows down I have another cell with the word "Remove" in it. In Range("C6:C20") I have the products description. The products description can range from 4 to 15 rows. The reference number cell is always in Column B and next to the first line of the product description and the remove cell is always 3 rows below the reference number. Once the data is added to the worksheet, a thin line border is inserted around the product description range. I want the user to be able to double click the cell with "Remove" in it next to the product description range and then a macro will delete the entire rows the range is located. I have the double click event already. I just need the loop to delete the range between the border. Any help pointing me in the right direction would greatly be appreciated!! THanks!! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Rows Between Border Lines
Thanks for your reply Joel!
I don't think that these lines of code are going to work for my application. I tried your code and it only deletes the first two rows, not sure why. I failed to mention that there may be several product descriptions each with its own "Reference Number" and "Remove" Cell next to it. I need the capability to double click any of the "Remove" Cells and it will delete the range next to it contained in the thin line border. Product description will range from 4 to 15 rows long. It may also help to mention that the Ref # Cells have a orange background color. Maybe it would help to count the rows between orange cells. I'm not sure how to do it though. I'll try to illustrate it below: Col. B Col. C ____________________________________ 1 Ref. 123 Line 1 of Description 2 Line 2 of Description 3 Line 3 of Description 4 Remove Line 4 of Description 5 Line 5 of Description 6 Line 6 of Description .. ..... Description .. ..... Description 15 Line 15 of Description ____________________________________ 16 Ref. 456 Line 1 of Description 17 Line 2 of Description 18 Line 3 of Description 19 Remove Line 4 of Description 20 Line 5 of Description 21 Line 6 of Description ____________________________________ If I have managed to confuse you even more let me know and maybe I could e-mail it to you so you can see what I am talking about. "Joel" wrote: You don't need a loop, just 2 lines off code LastRow = Range("C6").End(xlDown).Row Rows("6:" & LastRow).Delete "RyanH" wrote: I will try to sum all this up quickly so I do not ramble on. I am making a price quoting worksheet. In Cell B6, I have a "Reference Number", that represents a product, in a cell and 3 rows down I have another cell with the word "Remove" in it. In Range("C6:C20") I have the products description. The products description can range from 4 to 15 rows. The reference number cell is always in Column B and next to the first line of the product description and the remove cell is always 3 rows below the reference number. Once the data is added to the worksheet, a thin line border is inserted around the product description range. I want the user to be able to double click the cell with "Remove" in it next to the product description range and then a macro will delete the entire rows the range is located. I have the double click event already. I just need the loop to delete the range between the border. Any help pointing me in the right direction would greatly be appreciated!! THanks!! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Rows Between Border Lines
try this code.
Sub removerows() SelectRow = ActiveCell.Row If SelectRow < 1 Then FirstRow = Range("B" & (SelectRow - 1)).End(xlUp).Row Else FirstRow = SelectRow End If LastRow = Range("B" & (SelectRow + 1)).End(xlDown).Row LastRow = LastRow - 1 If LastRow = (Rows.Count - 1) Then LastRow = Range("C" & Rows.Count).End(xlUp).Row End If Rows(FirstRow & ":" & LastRow).Delete End Sub "RyanH" wrote: Thanks for your reply Joel! I don't think that these lines of code are going to work for my application. I tried your code and it only deletes the first two rows, not sure why. I failed to mention that there may be several product descriptions each with its own "Reference Number" and "Remove" Cell next to it. I need the capability to double click any of the "Remove" Cells and it will delete the range next to it contained in the thin line border. Product description will range from 4 to 15 rows long. It may also help to mention that the Ref # Cells have a orange background color. Maybe it would help to count the rows between orange cells. I'm not sure how to do it though. I'll try to illustrate it below: Col. B Col. C ____________________________________ 1 Ref. 123 Line 1 of Description 2 Line 2 of Description 3 Line 3 of Description 4 Remove Line 4 of Description 5 Line 5 of Description 6 Line 6 of Description . ..... Description . ..... Description 15 Line 15 of Description ____________________________________ 16 Ref. 456 Line 1 of Description 17 Line 2 of Description 18 Line 3 of Description 19 Remove Line 4 of Description 20 Line 5 of Description 21 Line 6 of Description ____________________________________ If I have managed to confuse you even more let me know and maybe I could e-mail it to you so you can see what I am talking about. "Joel" wrote: You don't need a loop, just 2 lines off code LastRow = Range("C6").End(xlDown).Row Rows("6:" & LastRow).Delete "RyanH" wrote: I will try to sum all this up quickly so I do not ramble on. I am making a price quoting worksheet. In Cell B6, I have a "Reference Number", that represents a product, in a cell and 3 rows down I have another cell with the word "Remove" in it. In Range("C6:C20") I have the products description. The products description can range from 4 to 15 rows. The reference number cell is always in Column B and next to the first line of the product description and the remove cell is always 3 rows below the reference number. Once the data is added to the worksheet, a thin line border is inserted around the product description range. I want the user to be able to double click the cell with "Remove" in it next to the product description range and then a macro will delete the entire rows the range is located. I have the double click event already. I just need the loop to delete the range between the border. Any help pointing me in the right direction would greatly be appreciated!! THanks!! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Rows Between Border Lines
You are the man. It seems to work beautifully. Can you expain one thing to
me though. I am learning VBA and I am confused on to things with this code. I thought you have to declare your variables (SelectRow, FirstRow,LastRow) using Dim "VariableName" As Long. Why don't you have to use it here in this code? Does the computer automatically define the range surrounded by the thin line border as a collection? If so, does this explain how you can you use the Count and End Properties? I just don't see how VBA knows the start and end of what to count. "Joel" wrote: try this code. Sub removerows() SelectRow = ActiveCell.Row If SelectRow < 1 Then FirstRow = Range("B" & (SelectRow - 1)).End(xlUp).Row Else FirstRow = SelectRow End If LastRow = Range("B" & (SelectRow + 1)).End(xlDown).Row LastRow = LastRow - 1 If LastRow = (Rows.Count - 1) Then LastRow = Range("C" & Rows.Count).End(xlUp).Row End If Rows(FirstRow & ":" & LastRow).Delete End Sub "RyanH" wrote: Thanks for your reply Joel! I don't think that these lines of code are going to work for my application. I tried your code and it only deletes the first two rows, not sure why. I failed to mention that there may be several product descriptions each with its own "Reference Number" and "Remove" Cell next to it. I need the capability to double click any of the "Remove" Cells and it will delete the range next to it contained in the thin line border. Product description will range from 4 to 15 rows long. It may also help to mention that the Ref # Cells have a orange background color. Maybe it would help to count the rows between orange cells. I'm not sure how to do it though. I'll try to illustrate it below: Col. B Col. C ____________________________________ 1 Ref. 123 Line 1 of Description 2 Line 2 of Description 3 Line 3 of Description 4 Remove Line 4 of Description 5 Line 5 of Description 6 Line 6 of Description . ..... Description . ..... Description 15 Line 15 of Description ____________________________________ 16 Ref. 456 Line 1 of Description 17 Line 2 of Description 18 Line 3 of Description 19 Remove Line 4 of Description 20 Line 5 of Description 21 Line 6 of Description ____________________________________ If I have managed to confuse you even more let me know and maybe I could e-mail it to you so you can see what I am talking about. "Joel" wrote: You don't need a loop, just 2 lines off code LastRow = Range("C6").End(xlDown).Row Rows("6:" & LastRow).Delete "RyanH" wrote: I will try to sum all this up quickly so I do not ramble on. I am making a price quoting worksheet. In Cell B6, I have a "Reference Number", that represents a product, in a cell and 3 rows down I have another cell with the word "Remove" in it. In Range("C6:C20") I have the products description. The products description can range from 4 to 15 rows. The reference number cell is always in Column B and next to the first line of the product description and the remove cell is always 3 rows below the reference number. Once the data is added to the worksheet, a thin line border is inserted around the product description range. I want the user to be able to double click the cell with "Remove" in it next to the product description range and then a macro will delete the entire rows the range is located. I have the double click event already. I just need the loop to delete the range between the border. Any help pointing me in the right direction would greatly be appreciated!! THanks!! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Rows Between Border Lines
If Option Explicit is at the top of your code modules then the compiler will
force you to Dim your variables. Without Option Explicit, all un-Dimmed variables are understood as Variant, and the compiler does not complain. Joel was just presenting a workable code. It is up to you whether to use Option Explicit and Dim your variables, or not. The second answer is no to automatically defining a range. The code assumes that the cursor is on the word (cell) "Remove" of the section that you want deleted, before you run the code. Sub removerows() SelectRow = ActiveCell.Row 'the row your cursor is on 'This next IF statement traps whether the cursor is on row 1 or not If SelectRow < 1 Then FirstRow = Range("B" & (SelectRow - 1)).End(xlUp).Row 'If not, then from the row above the cursor, in column B, ' find the row of the next word up (FirstRow of section) Else FirstRow = SelectRow 'If cursor is on row 1 then use that row End If LastRow = Range("B" & (SelectRow + 1)).End(xlDown).Row 'From the row below the cursor find the row of the next word down LastRow = LastRow - 1 'and subtract 1 row (LastRow of section) 'This next bit checks to see if you are near the bottom of the 'sheet and adjusts accordingly. If LastRow = (Rows.Count - 1) Then LastRow = Range("C" & Rows.Count).End(xlUp).Row End If Rows(FirstRow & ":" & LastRow).Delete End Sub Mike F "RyanH" wrote in message ... You are the man. It seems to work beautifully. Can you expain one thing to me though. I am learning VBA and I am confused on to things with this code. I thought you have to declare your variables (SelectRow, FirstRow,LastRow) using Dim "VariableName" As Long. Why don't you have to use it here in this code? Does the computer automatically define the range surrounded by the thin line border as a collection? If so, does this explain how you can you use the Count and End Properties? I just don't see how VBA knows the start and end of what to count. "Joel" wrote: try this code. Sub removerows() SelectRow = ActiveCell.Row If SelectRow < 1 Then FirstRow = Range("B" & (SelectRow - 1)).End(xlUp).Row Else FirstRow = SelectRow End If LastRow = Range("B" & (SelectRow + 1)).End(xlDown).Row LastRow = LastRow - 1 If LastRow = (Rows.Count - 1) Then LastRow = Range("C" & Rows.Count).End(xlUp).Row End If Rows(FirstRow & ":" & LastRow).Delete End Sub "RyanH" wrote: Thanks for your reply Joel! I don't think that these lines of code are going to work for my application. I tried your code and it only deletes the first two rows, not sure why. I failed to mention that there may be several product descriptions each with its own "Reference Number" and "Remove" Cell next to it. I need the capability to double click any of the "Remove" Cells and it will delete the range next to it contained in the thin line border. Product description will range from 4 to 15 rows long. It may also help to mention that the Ref # Cells have a orange background color. Maybe it would help to count the rows between orange cells. I'm not sure how to do it though. I'll try to illustrate it below: Col. B Col. C ____________________________________ 1 Ref. 123 Line 1 of Description 2 Line 2 of Description 3 Line 3 of Description 4 Remove Line 4 of Description 5 Line 5 of Description 6 Line 6 of Description . ..... Description . ..... Description 15 Line 15 of Description ____________________________________ 16 Ref. 456 Line 1 of Description 17 Line 2 of Description 18 Line 3 of Description 19 Remove Line 4 of Description 20 Line 5 of Description 21 Line 6 of Description ____________________________________ If I have managed to confuse you even more let me know and maybe I could e-mail it to you so you can see what I am talking about. "Joel" wrote: You don't need a loop, just 2 lines off code LastRow = Range("C6").End(xlDown).Row Rows("6:" & LastRow).Delete "RyanH" wrote: I will try to sum all this up quickly so I do not ramble on. I am making a price quoting worksheet. In Cell B6, I have a "Reference Number", that represents a product, in a cell and 3 rows down I have another cell with the word "Remove" in it. In Range("C6:C20") I have the products description. The products description can range from 4 to 15 rows. The reference number cell is always in Column B and next to the first line of the product description and the remove cell is always 3 rows below the reference number. Once the data is added to the worksheet, a thin line border is inserted around the product description range. I want the user to be able to double click the cell with "Remove" in it next to the product description range and then a macro will delete the entire rows the range is located. I have the double click event already. I just need the loop to delete the range between the border. Any help pointing me in the right direction would greatly be appreciated!! THanks!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to delete rows which is blank and which has ======== ( lines) | Excel Discussion (Misc queries) | |||
| Delete Columns if rows 8 & 9 are blank and place border | Excel Programming | |||
| Border lines disappear | Excel Worksheet Functions | |||
| how to print border lines? | Excel Discussion (Misc queries) | |||
| Border lines not printing | Excel Worksheet Functions | |||
Linear Mode
