Solved: Delete function failing

[lifeson]

I have this routine which has worked fine until I added avriable for the worksheet to use.

[vba]Sub DeleteRows(ws As Worksheet, criteria As String, col As Long)
Dim rTable As Range
Application.ScreenUpdating = False
On Error Resume Next
'Determine the table range

With Selection
If ws.Cells.Count > 1 Then
Set rTable = Selection
Else
Set rTable = .CurrentRegion
On Error GoTo 0
End If
End With
'Remove any existing AutoFilters
ws.AutoFilterMode = False

'Filter table based on vCriteria using the relative column position stored in lCol.
rTable.AutoFilter Field:=col, Criteria1:=criteria

'Delete all rows that are NOT hidden by AutoFilter.
rTable.Offset(2, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
'Remove AutoFilters
ws.AutoFilterMode = False
On Error GoTo 0

End Sub[/vba]

I have tried calling the routine linke this
(Where 'parent' is correctly defined)

[vba]DeleteRows(Worksheets("Treeview"), parent, 2)[/vba]

And I have tried calling it like this

[vba]DeleteRows(wsTree,parent,2)[/vba]

Where the worksheet is defined as
[vba]Public wsTree as worksheet
Set wsTree = thisworkbook.worksheets("TreeView")[/vba]

[lifeson]

Ok
I think I have spotted it
[vba]Sub DeleteRows(ws As Worksheet, criteria As String, col As Long)
Dim rTable As Range
Application.ScreenUpdating = False
Dim name As String
'added this and it seems to work
ws.Select

On Error Resume Next
'Determine the table range

With Selection
If ws.Cells.Count > 1 Then
Set rTable = Selection
Else
Set rTable = .CurrentRegion
On Error GoTo 0
End If
End With
'Remove any existing AutoFilters
ws.AutoFilterMode = False

'Filter table based on vCriteria using the relative column position stored in lCol.
rTable.AutoFilter Field:=col, Criteria1:=criteria

'Delete all rows that are NOT hidden by AutoFilter.
rTable.Offset(2, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
'Remove AutoFilters
ws.AutoFilterMode = False
On Error GoTo 0

End Sub[/vba]

But I still have a problem with the routine that I would like help with please.

When it runs it deletes all the rows correctly but also deletes the header row which I would like to keep

I thought this bit
[VBA]rTable.Offset(2, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete[/VBA]
Should mean the header (top) row should remain but obviously doesn't work

[Bob Phillips]

Try this

[vba]

Intersect(rTable.SpecialCells(xlCellTypeVisible), rTable.Offset(1, 0)).EntireRow.Delete
[/vba]

[mdmackillop]

Try
[VBA]
With Selection
If ws.Cells.Count > 1 Then
Set rtable = Selection
Else
Set rtable = .CurrentRegion
On Error GoTo 0
End If
End With
Dim DelTable as range
Set DelTable = rtable.Offset(2)
'Remove any existing AutoFilters
ws.AutoFilterMode = False

'Filter table based on vCriteria using the relative column position stored in lCol.
rtable.AutoFilter Field:=col, Criteria1:=criteria

'Delete all rows that are NOT hidden by AutoFilter.
DelTable.SpecialCells(xlCellTypeVisible).EntireRow.Delete
'Remove AutoFilters
ws.AutoFilterMode = False
On Error GoTo 0

[/VBA]

[lifeson]

Thanks guys but no joy.
Tried XLD's - this option did not delete anything

Tried mdm's and this gave the same results ie the header row was deleted as well.

[Bob Phillips]

Can you post your workbook then, mine worked in my tests.

[lifeson]

OK, but it will take some time to build a demo

[lifeson]

Quicker than I thought to compile demo

I thought I had cracked the original problem but I haven't

[Bob Phillips]

Try this

[vba]

Sub DeleteRows(ws As Worksheet, criteria As Variant, col As Long)
Dim rTable As Range
Dim LastRow As Long

On Error Resume Next
'Determine the table range

With ws

.Select
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Set rTable = .Range("A1").Resize(LastRow, 4)

'Remove any existing AutoFilters
.AutoFilterMode = False

'Filter table based on vCriteria using the relative column position stored in lCol.
rTable.AutoFilter Field:=col, Criteria1:=criteria

'Delete all rows that are NOT hidden by AutoFilter.
Intersect(rTable.SpecialCells(xlCellTypeVisible), rTable.Offset(1, 0)).EntireRow.Delete

'Remove AutoFilters
.AutoFilterMode = False
End With
End Sub
[/vba]

[lifeson]

As usual thanks XLD
Thats the second time I have noticed you have used the .resize option to define a range, looks quite handy

[Bob Phillips]

You must have missed most of my posts <vbg> - I use it all the time.