| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering and removing rows
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filtering and removing rows
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Filtering and removing rows
On 3 Mar, 16:38, "Tom Ogilvy" wrote:
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- Hide quoted text - - 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 Befo=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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sorting or filtering the rows | Excel Discussion (Misc queries) | |||
| How do I Copy and Paste onto Same Rows after filtering out rows. | Excel Discussion (Misc queries) | |||
| Filtering Rows ? | Excel Worksheet Functions | |||
| Removing (deleting, filtering) lines in a pattern . . . | Excel Discussion (Misc queries) | |||
| Removing Empty Rows and selecting Specific Rows | Excel Programming | |||
Linear Mode
