It is hard to follow what you are doing, but try something like this:

Sub Main()
DeleteFilteredRows Worksheets("Invoice Detail"), 26, "Split"
DeleteFilteredRows Worksheets("Invoice Detail - Split"), 26, 17.5
DeleteFilteredRows Worksheets("Invoice Detail - Split"), 26, 5
End Sub


Public Function DeleteFilteredRows(sh As Worksheet, col As Long, crit As
Variant)
Dim rng As Range, rng1 As Range, rng2 As Range

With sh
.AutoFilterMode = False
.Cells.AutoFilter Field:=col, Criteria1:=crit
Set rng = .AutoFilter.Range
Set rng1 = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
On Error Resume Next
Set rng2 = rng1.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then
rng1.EntireRow.Delete
End If
.AutoFilterMode = False
End With
End Function


--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Hi,

I have built a macro that filters my sheet; I then want to remove
these rows leaving no spaces and only the rows with remaining
criteria.

I can do all this, no problem. The problem occurs when the rows
change, as this is a variable set of data.

When I send the excel to the cell one below A1, while filtered, it
always returns a reference. i.e. below on line 5.

I need this to be variable.

Can anyone please help a man on the brink of tears......?

Thank you in advance


' Sub removing_split_Rate()

Sheets("Invoice Detail").Select
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=26, Criteria1:="Split"
Range("A92").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A575").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
Selection.AutoFilter Field:=26
Range(Selection, Selection.End(xlUp)).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range("A1").Select
Selection.End(xlUp).Select
Range("A2").Select
Sheets("Invoice Detail - Split").Select
Range("A2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=26, Criteria1:="17.5"
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=26, Criteria1:="5"
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("AA40").Select
Selection.AutoFilter Field:=26
Range("A2").Select
Selection.End(xlToLeft).Select
Selection.AutoFilter
Sheets("Invoice Detail").Select
Selection.AutoFilter