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" <(E-Mail Removed)> wrote in message
news:10F4114A-E31B-43F7-BAB2-(E-Mail Removed)...
> 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!!
|
Similar Threads
