Conditional delete rows on outside cell content - pls help?

Chav · Mar 18, 2011

[FONT=&quot]Am a newbie to the board but using excel for automating my report forms.
Removing rows from the report though is a tough nut to crack for me.

I'd like to remove a block of 7 rows, say 68 thru 74 if cell J67 says: "and no" which means we do not have this component, so we don't need to describe it.

The macro to delete 7 rows works like a charm, now I am at a loss how to connect it to the J67 content.

The macro highlights the seven rows and removes them with "clear all" command, then places a top border on previous line where border was removed.

[/FONT] ActiveCell.Rows("1:7").EntireRow.Select
Selection.Clear
ActiveCell.Range("A1:M1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

[FONT=&quot] All help tremendously appreciated, [/FONT]

jbeaucaire · Mar 18, 2011

The "clear" you did should remove all the borders, so I think this is all you need:

Code:

Sub Test()

If Range("J67") = "and no" Then
    Range("A68:M74").EntireRow.Clear
    Range("A68:M68").Borders(xlEdgeTop).Weight = xlThin
End If

End Sub

Chav · Mar 18, 2011

jbeaucaire said:
The "clear" you did should remove all the borders, so I think this is all you need:

Code:

Sub Test() If Range("J67") = "and no" Then Range("A68:M74").EntireRow.Clear Range("A68:M68").Borders(xlEdgeTop).Weight = xlThin End If End Sub

Hello Jerry,
Thank you for taking the time to respond. What you sent me is simple and elegant. It could run when I started the macro with Alt F8, and the trigger cell was in "and no" option. Going to the "and no" option alone did not trigger the subroutine, though. What is the way for me to upload the worksheet up here?
With gratitude, Chav

jbeaucaire · Mar 18, 2011

That macro is a standard macro that goes in a regular code module (Insert > Module).

If you wanted a macro to watch cell J67 and SELF-trigger, then you would need to put this version into the sheet module itself:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

For Each cell In Target
    If cell.Address = "$J$67" And cell = "and no" Then
        Application.EnableEvents = False
            Range("A68:M74").EntireRow.Clear
            Range("A68:M68").Borders(xlEdgeTop).Weight = xlThin
        Application.EnableEvents = True
    End If
Next cell

End Sub

Conditional delete rows on outside cell content - pls help?

Chav

New Member

Excel Facts

jbeaucaire

Well-known Member

Chav

New Member

jbeaucaire

Well-known Member

Similar threads

Forum statistics

Share this page

Conditional delete rows on outside cell content - pls help?

Chav

New Member

Excel Facts

jbeaucaire

Well-known Member

Chav

New Member

jbeaucaire

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock