PDA

View Full Version : Solved: Delete function failing



lifeson
06-23-2008, 04:48 AM
I have this routine which has worked fine until I added avriable for the worksheet to use.

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

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

DeleteRows(Worksheets("Treeview"), parent, 2)

And I have tried calling it like this

DeleteRows(wsTree,parent,2)

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

lifeson
06-23-2008, 05:09 AM
Ok
I think I have spotted it
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

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

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
rTable.Offset(2, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Should mean the header (top) row should remain but obviously doesn't work:banghead:

Bob Phillips
06-23-2008, 05:43 AM
Try this



Intersect(rTable.SpecialCells(xlCellTypeVisible), rTable.Offset(1, 0)).EntireRow.Delete

mdmackillop
06-23-2008, 05:50 AM
Try

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

lifeson
06-23-2008, 06:27 AM
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
06-23-2008, 06:31 AM
Can you post your workbook then, mine worked in my tests.

lifeson
06-23-2008, 06:43 AM
OK, but it will take some time to build a demo

lifeson
06-23-2008, 07:06 AM
Quicker than I thought to compile demo

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

Bob Phillips
06-23-2008, 08:08 AM
Try this



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

lifeson
06-23-2008, 11:07 AM
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
06-23-2008, 11:52 AM
You must have missed most of my posts <vbg> - I use it all the time.