Removing rows with "IF" command, how can I do that?

Chav · Mar 17, 2011

Pls help - After several nights of exploring menus and and commands I am lost. Found some great tools to automate my report form, and need to remove a block of rows "if" there is no "such and such" component, or put in that block if component is there. It cannot be that hard, but there seems to be no obvious answer. Any suggestion greatly appreciated!!!

Tom Urtis · Mar 18, 2011

Chav said:
...need to remove a block of rows "if" there is no "such and such" component, or put in that block if component is there.

Does that really make sense to you?

Try explaining with an example of what a "such and such" is, what a component is, and under what conditions you expect to remove a block of rows, and why. The key here is (a) understanding what you are referring to, and (b) getting a handle on the logic of what your expected results are, based on some scenario you'd describe as the reason for carrying out this anti-such-and-such-component action.

Chav · Mar 18, 2011

Thank you for responding, Tom.
Here is the detail: [FONT=&quot]I'd like to remove a block of 7 rows, say 68 through 74 when dropdown cell J67 switches to: "and no" which means we do not have this component, so we don't need to write about it. The sub for removing the rows with "clear all" command works well, now I wonder how to connect the condition of the dropdown cell in J67 to trigger the "clear" sub[/FONT].

Sub Remove7Rows()
 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

Can you suggest a solution?

Tom Urtis · Mar 18, 2011

Still not clear about the relevance of your posted macro which clears rows 1:7 while you are talking about rows 68:74, or why you are deleting and adding borders. Be that as it may, for starters, when "and no" is entered into cell J67, the contents of cells in rows 68:74 will be cleared with the below code.

For the worksheet where you want this to happen, right click on the sheet tab, and left-click to select View Code. Paste the following code into the large white area that is the worksheet module. Press the Alt+Q keys to return to the worksheet.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$J$67" Then Exit Sub
If Target.Value = "and no" Then Rows("68:74").ClearContents
End Sub

Removing rows with "IF" command, how can I do that?

Chav

New Member

Excel Facts

Tom Urtis

MrExcel MVP

Chav

New Member

Tom Urtis

MrExcel MVP

Similar threads

Forum statistics

Share this page

Removing rows with "IF" command, how can I do that?

Chav

New Member

Excel Facts

Tom Urtis

MrExcel MVP

Chav

New Member

Tom Urtis

MrExcel MVP

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