Deleting rows in Excel

[julesl]

Hi

I'm trying to delete rows in excel which meet certain criteria without much success.

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

I need to delete all the rows in the spreadsheet that contain nothing in column F. The data will be refreshed on a daily basis so will have a varying number of rows.

I'm trying to create a macro in Excel 97, that when run, will delete all the rows but my VB knowledge is very limited.

Any help will be much appreciated.

Thanks

[Ecniv]

Do until lngLoop>100
    if len(cells(lngLoop,6))=0 then 
       cells(lngloop,1).entirerow.delete
    else
        lngloop=lngloop+1
    end if
loop
next

Something like that. Experiment.
Please note that this should work in vba (excel), it also does need to be assigned to the right objects which you will need to set up before calling.


Vince

[julesl]

Hi

Thank you for your reply.

I had a look on the internet before I received your reply and found the following code:-

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

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