Thread: conditional row delete and autofilter

Help! I've been struggling with this for the past few weeks, and I'm hoping one of y'all can help me get past this.

What the macro is supposed to do is to load the file (customer.xls), delete columns and rows of data that aren't needed, and then delete additional rows of data that can be excluded based on conditional criteria.

If I run the macro as a whole, it deletes all rows save for the header row and leaves the auto filter set to 'on'. This despite turning the autofilter off at the conclusion of each of the subs. If I comment out the portion at the top which calls the subs and run the subs individually, it works, but with each sub i have to go back to the spreadsheet and manually turn the autofilter off.

The command to turn the autofilter off is included in each of the subs, but for some reason it isn't doing it. I have included a sample of the data that I'm working with, and the macro I'm using. Thanks in advance.

Marcus

HTML Code: 

Sub LoadFile()
' LoadFile Macro
' Macro written 8/28/2007 by marcus
    Workbooks.Open Filename:= _
        "M:\Schedule Initiative Documents\customer.xls"
    Call DeleteRowsandColumns
    Call DeleteInactiveJobs
    Call DeleteJobStatusNEContract
    Call DeleteCType
    Call DeleteJobEndDate
    Call AddNewColumns

End Sub

Sub DeleteRowsandColumns()
' Delete Rows And Columns Macro
' Macro written 8/28/2007 by marcus
    Rows("1:20").Delete
    Columns("A:A").Delete
    Columns("B:H").Delete
    Columns("G:M").Delete
    Columns("H:N").Delete
    Columns("I:M").Delete
    Columns("P:W").Delete
    Columns("S:T").Delete
    Columns("U:V").Delete
End Sub

Sub DeleteInactiveJobs()
'   This Sub deletes all rows where Hidden is other than "N"

'   Remove any existing AutoFilters.
    ActiveSheet.AutoFilterMode = False
    
'   Show only (filter) Column T transactions by non Y entries.
    Columns("T").Select
    Selection.AutoFilter Field:=1, Criteria1:="<>N"
    
'   Hide Row 1 so it's NOT deleted by the next line of code.
    Rows("1").EntireRow.Hidden = True
    
'   Delete all rows that are NOT hidden.
    Columns("T").SpecialCells(xlCellTypeVisible).EntireRow.Delete
    
'   Remove AutoFilters
    ActiveSheet.AutoFilterMode = False
    
'   Unhide Row 1.
    Rows("1").EntireRow.Hidden = False
    
End Sub

Sub DeleteJobStatusNEContract()

'   This Sub deletes all rows where JobStatus is other than "Contract (2)"

'   Remove any existing AutoFilters.
    ActiveSheet.AutoFilterMode = False

'   Show only (filter) Column R transactions by non 2 entries.
    Columns("R").Select
    Selection.AutoFilter Field:=1, Criteria1:="<>2"

'   Hide Row 1 so it's NOT deleted by the next line of code.
    Rows("1").EntireRow.Hidden = True

'   Delete all rows that are NOT hidden.
    Columns("R").SpecialCells(xlCellTypeVisible).EntireRow.Delete

'   Remove AutoFilters
    ActiveSheet.AutoFilterMode = False

'   Unhide Row 1.
    Rows("1").EntireRow.Hidden = False

End Sub

Sub DeleteCType()
'   This Sub deletes all rows where CTYPE is = "N/A"

'   Remove any existing AutoFilters.
    ActiveSheet.AutoFilterMode = False

'   Show only (filter) Column R transactions by non 2 entries.
    Columns("G").Select
    Selection.AutoFilter Field:=1, Criteria1:="N/A"

'   Hide Row 1 so it's NOT deleted by the next line of code.
    Rows("1").EntireRow.Hidden = True

'   Delete all rows that are NOT hidden.
    Columns("R").SpecialCells(xlCellTypeVisible).EntireRow.Delete

'   Remove AutoFilters
    ActiveSheet.AutoFilterMode = False

'   Unhide Row 1.
    Rows("1").EntireRow.Hidden = False

End Sub

Sub DeleteJobEndDate()
'   This Sub deletes all rows where JobEnd Date <> " "

'   Remove any existing AutoFilters.
    ActiveSheet.AutoFilterMode = False

'   Show only (filter) Column S transactions by non Blank entries.
    Columns("S").Select
    Selection.AutoFilter Field:=1, Criteria1:="<> "

'   Hide Row 1 so it's NOT deleted by the next line of code.
    Rows("1").EntireRow.Hidden = True

'   Delete all rows that are NOT hidden.
    Columns("S").SpecialCells(xlCellTypeVisible).EntireRow.Delete

'   Remove AutoFilters
    ActiveSheet.AutoFilterMode = False

'   Unhide Row 1.
    Rows("1").EntireRow.Hidden = False

End Sub

Sub AddNewColumns()
'   This Sub Adds Columns U and V (CALC SVC DATE and NEXT SVC DATE)

    ActiveWindow.LargeScroll ToRight:=1
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "CALC SVC DATE"
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "NEXT SVC DATE"
    Range("W1").Select

End Sub

Marcus

I put a copy of your file on my PC, then put your code into a blank workbook. Changed the path and then ran your main code.

Seemed to work OK. Still had data at the end, and the autofilter was not active.

One thing - is your file really stored as a spreadsheet, not just have a .xls suffix? When I tried to save it, the file went to a default text mode...


rylo

hey rylo - interesting. on my home computer, my laptop and the office computer i get the exact same results. the macro never turns the autofilter off. i can do it manually, but when run either as one macro or several small one's it always leaves the autofilter on. i'm wondering if this could be a configuration problem? i'm running excel 2003 if that's of any help.

yes, when quickbooks exports the file it does so as a text file which i apply the xls extension to. it won't affect the reports that the macro will generate at the end of the routine so i haven't bothered trying to do anything with it.

thanks for your help - marcus

Greetings - I've been working on this macro most of the day and evening (again), looking at other threads with similar issues, and though I don't yet know how to resolve the autofilter switch and delete issues, I believe it may have something to do with where the macro is relative to where it loads the customer data.

When I initially bring up excel I Alt-F11 and I see a tree structure that consists of the following:

VBAProject (Book1)
Microsoft Excel Objects
Sheet1
Sheet2
Sheet3
ThisWorkbook
VBA Personal Project (Personal.xls)
Microsoft Excel Objects
Sheet1
ThisWorkbook

Do I need to somehow get rid of the Personal Project, or is there some particular place I should be putting the macro? Hopefully this is the missing piece of information. Thanks in advance