Delete Rows Between Border Lines

[RyanH]

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!!

 

[Joel]

You don't need a loop, just 2 lines off code

LastRow = Range("C6").End(xlDown).Row
Rows("6:" & LastRow).Delete

 

[RyanH]

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]

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]

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.

 

[Mike Fogleman]

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