Filtering and removing rows

[craiglittleperth]

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

 

[Tom Ogilvy]

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

 

[craiglittleperth]

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














- Show quoted text -

Hi tom,

Thanks for this. I am really sorry, I don't know how to drop it into
my current script.

I am sorry, i have been gluing this together for months...

' Create two sheets for invoice split and summary split?

Sheets("Invoice Summary").Select
Sheets("Invoice Summary").Copy Before:=Sheets(5)
Sheets("Invoice Detail").Select
Sheets("Invoice Detail").Copy After:=Sheets(6)
Sheets("Invoice Summary (2)").Select
Sheets("Invoice Summary (2)").Name = "Invoice Summary - Split"
Sheets("Invoice Detail (2)").Select
Sheets("Invoice Detail (2)").Name = "Invoice Detail - Split"
Sheets("VAT Breakdown").Select



End Sub

I need to add it into this sub.

You are correct in thinking that there are 3 critreia on two different
tabs. These don't need filtered if this VBA can remove these
automatically?

Again thank you for looking at this.

Craig