IF function

[Luc]

Hi all,

Is it possible to have an IF function that deletes a row
if a condition is returned?

I have a list of stock, with sub-totals throughout. I
want to delete all rows with sub-totals, so that i am only
left with the actual items. So, in the example below, i
would want to delete rows 3 and 5.

A B
1 item1a £5
2 item1b £4
3 sub-total £9
4 item2 £2
5 item3a £4
6 item3b £6
7 sub-total £10

the rows i want to delete, ll have sub-total written in
them.

thanks for any help,

Luc
6

 

[Jason Morin]

Not possible with a function, but you could run this
macro:

Sub DeleteRows()
Dim iLastRow As Integer
Dim i As Integer

Application.ScreenUpdating = False

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
With Cells(i, "A")
If .Value = "sub-total" Then
.EntireRow.Delete
End If
End With
Next i

Application.ScreenUpdating = True

End Sub

 

[Don Guillett]

you would need a macro
for each c in selection
if left(c,3)="sub" then c.entirerow.delete
next

But why not set up your data without the subtotals and then use
data>subtotals which can be hidden or shown. Again, I would use a macro. An
example. Record your own and clean up.

x = Cells(Rows.Count, 1).End(xlUp).Row + 1
ActiveSheet.Range("a5:i" & x).Subtotal GroupBy:=2, Function:=xlSum, _
TotalList:=Array(8, 9), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True

--
Don Guillett
SalesAid Software
[email protected]
Hi all,

Is it possible to have an IF function that deletes a row
if a condition is returned?

I have a list of stock, with sub-totals throughout. I
want to delete all rows with sub-totals, so that i am only
left with the actual items. So, in the example below, i
would want to delete rows 3 and 5.

A B
1 item1a £5
2 item1b £4
3 sub-total £9
4 item2 £2
5 item3a £4
6 item3b £6
7 sub-total £10

the rows i want to delete, ll have sub-total written in
them.

thanks for any help,

Luc
6

 

[Frank Stone]

hi,
by function do you mean a formula or a code function.
if formula...no. sorry. a formula returns a value, it can
not perform an action like delete a row.
if you mean code, yes. i think that would be your only
option.
Sub Delsubtotal()
Range("A2").select
Set Item1 = range("A2")
do while not isempty(Item1)
set Item2 = Item1.offset(1,0)
If Item1.value = "sub-Total" then
Item1.entiretow.delete
set Item1 = Item2
else
set Item1 = Item2
end if
loop
end sub
tools>macro>macros
enter DelSubTolal in the top text box and click the create
button. copy all the code above between sub and end sub
and paste it in the vb editor.
to run...tools>Macro>macros. click Delsubtoals in the name
box and click the run button.
regards
Frank

 

[Luc]

Thanks to all three of you for that.

unfortunately...

excel is now telling me that macros in the project have been disabled!!
does that my IT department may have disabled all Macros?



hi,
by function do you mean a formula or a code function.
if formula...no. sorry. a formula returns a value, it can
not perform an action like delete a row.
if you mean code, yes. i think that would be your only
option.
Sub Delsubtotal()
Range("A2").select
Set Item1 = range("A2")
do while not isempty(Item1)
set Item2 = Item1.offset(1,0)
If Item1.value = "sub-Total" then
Item1.entiretow.delete
set Item1 = Item2
else
set Item1 = Item2
end if
loop
end sub
tools>macro>macros
enter DelSubTolal in the top text box and click the create
button. copy all the code above between sub and end sub
and paste it in the vb editor.
to run...tools>Macro>macros. click Delsubtoals in the name
box and click the run button.
regards
Frank

 

[swatsp0p]

It could be your IT has cut off macro use.... OR... you can check you
local security setting by going to Tools>Options and clicking on th
"Security" tab. at the bottom is the "Macro Security...." button.
Click that and verify your level is either Medium or High. If macro
still won't work at Medium, check with your IT guru for help.

HT

 

[Dave Peterson]

Just to add.

If you toggle this setting to allow macros to run, you'll have to close your
workbook and reopen it.

And answer yes to allow macros if you're prompted.

 

[Luc]

Thanks guys, that worked a treat and allowed me to run the macro.

BUT

excel now tells me there is a run-time error 13. when i go in to Vb to edit
it highlights in yellow the IFItem line below. This is what i typed in (i
changed the range to cover the relevant cells)

Sub DelSubTotal()
Range("D24000").Select
Set Item1 = Range("D24000")
Do While Not IsEmpty(Item1)
Set Item2 = Item1.Offset(1, 0)
If Item1.Value = "Sub Total" Then
Item1.entiretow.Delete
Set Item1 = Item2
Else
Set Item1 = Item2
End If
Loop
End Sub

Just to add.

If you toggle this setting to allow macros to run, you'll have to close your
workbook and reopen it.

And answer yes to allow macros if you're prompted.

 

[Dave Peterson]

When you're deleting rows, it's usually lots easier to start at the bottom and
work your way up.

If you start at the top and go downward, then you have to make sure you keep
track of what row you're on--and it gets to be a pain since when you delete the
row, the remaining rows move up one.

Here's a sample:

Option Explicit
Sub DelSubTotal2()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

With ActiveSheet
FirstRow = 2
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1
If LCase(.Cells(iRow, "D").Value) = LCase("sub total") Then
.Rows(iRow).Delete
End If
Next iRow
End With

And are you sure "sub total" is two words?

End Sub

Thanks guys, that worked a treat and allowed me to run the macro.

BUT

excel now tells me there is a run-time error 13. when i go in to Vb to edit
it highlights in yellow the IFItem line below. This is what i typed in (i
changed the range to cover the relevant cells)

Sub DelSubTotal()
Range("D24000").Select
Set Item1 = Range("D24000")
Do While Not IsEmpty(Item1)
Set Item2 = Item1.Offset(1, 0)
If Item1.Value = "Sub Total" Then
Item1.entiretow.Delete
Set Item1 = Item2
Else
Set Item1 = Item2
End If
Loop
End Sub

Just to add.

If you toggle this setting to allow macros to run, you'll have to close your
workbook and reopen it.

And answer yes to allow macros if you're prompted.