Automatically deleting rows in excel

[julesl]

Hi

I'm trying to automatically delete rows in excel which meet certain criteria.

I have a spreadsheet with 13 columns and column F is an indicator which contains either "mortgage" or blank.

I have created the following code using Thread 707-687994:-

Sub deletenonmort()
lRow = Cells(65536, 6).End(xlUp).Row
Range("A1:M" & lRow).AutoFilter Field:=6, Criteria1:=""
Rows("2:" & lRow).Delete Shift:=xlUp
Selection.AutoFilter Field:=6
End Sub

This works fine until it reaches the last row that contains "mortgage" and then doesn't delete any more blank rows after that.

I have next to no knowledge of VBA so any help would be much appreciated.

Thanks

 

[SkipVought]

Hi,

That's because lRow is defined as the LAST ROW in COLUMN 6 with data in it.

If you want lRow to be the LAST ROW IN THE TABLE...

with activesheet.usedrange
  lRow = range(cells(.row, 1), Cells(.rows.count, 1)).row
end with

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[julesl]

Hi

Thank you for your reply.

Sorry to sound completely stupid but which bit of my code do I need to replace with your suggestion.

When I said I had next to no knowledge of VBA I wasn't joking!!!!

Thanks for your help

Jules

 

[SkipVought]

Sub deletenonmort()[b]
with activesheet.usedrange
  lRow = range(cells(.row, 1), Cells(.rows.count, 1)).row
end with[/b]
    Range("A1:M" & lRow).AutoFilter Field:=6, Criteria1:=""
    Rows("2:" & lRow).Delete Shift:=xlUp
    Selection.AutoFilter Field:=6
End Sub

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[julesl]

Hi

Thanks for the code.

I've tried running it but I get a run time error 1004 saying "Microsoft Excel cannot insert or delete a column in a list while the Autofilter command is turned on."

This is the code debug highlights:-
"Rows("2:" & lRow).Delete Shift:=xlUp"

Any help please?

Thanks

Jules

 

[julesl]

Hi

I've just tried re-running the macro and I still get a run time error 1004 but this time the message was "Autofilter method of range class failed."

Sub delenonmt2()
With ActiveSheet.UsedRange
lRow = Range(Cells(.Row, 1), Cells(.Rows.Count, 1)).Row
End With
Range("A1:M" & lRow).AutoFilter Field:=6, Criteria1:=""
Rows("2:" & lRow).Delete Shift:=xlUp
Selection.AutoFilter Field:=6
Selection.AutoFilter
End Sub

Can anyone help please?

Thanks

Jules

 

[SkipVought]

   With ActiveSheet.UsedRange
     lRow = .Rows.Count
   End With
   Range("A1:E" & lRow).AutoFilter Field:=5, Criteria1:=""
   Rows("2:" & lRow).Delete Shift:=xlUp

modify the refereces to suite your application

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}