AdvancedFilter result range EXCLUDING input column title

[S3RG3]

Hi Everyone,

I am new to the forum - beginner to intermediate level VBA user and advanced Excel user - looking forward to helping and being helped on this forum =)

My first question is this - I am trying to use AdvancedFilter function to parse a range of cells from one sheet into another (via VBA), however, I need to exclude the title of the input range from my results...

VB:
Worksheets("Worksheet1").Range("J2" & ":J" & LastrowJ).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Worksheet2").Range("D1819"), CopyToRange:=Worksheets("Worksheet3").Range("A" & Lastrowa), Unique:=True


Appendix: J2 is the header/title of my input range and LastrowJ is the value of the last cell; D18 : D19 contains filtering criteria; Lastrowa is the value of the first cell to where my data is copied/filtered through.

I could simply refer my input range to start one cell AFTER the title, however, that will disallow my CriteriaRange to be applied correctly during filtering (e.g. CriteriaRange contains information on the column header used for filtering).

Alternatively, I could point my criteria to the first cell in the input range, instead of the title, but that is least desirable...

If anyone has any ideas/suggestions - please share - I will be very much obliged!

[mikerickson]

AdvancedFilter will move the headers to the CopyToRange.
But, once it has done that, that row of the output can be blanked or deleted.

[Paul_Hossler]

In Excel 2016, doing the operation manually I get an error that says "You can only copy filtered data to the active sheet"

[mdmackillop]

Sub Test()
    
    Dim Data As Range, crit As Range, Tgt As Range
    
    Set Data = Sheet1.Range("A7").CurrentRegion
    Set crit = Sheet1.Range("A1").CurrentRegion
    Set Tgt = Sheet2.Range("A1")
    
    Data.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=crit
    Data.Offset(1).SpecialCells(12).Copy Tgt
    Data.Parent.ShowAllData
End Sub

[S3RG3]

AdvancedFilter will move the headers to the CopyToRange.
But, once it has done that, that row of the output can be blanked or deleted.

Whenever I run the following delete macro, it works in the VBA step/break mode, however, once I run it through the macro button, it doesn't work (perhaps advanced filter runs again albeit being before in the code and overwrites it):

Dim Deleterow as Long
Deleterow = Application.WorksheetFunction.Match("Mycase", Worksheets("Sheet3").Range("A:A"), 0)
Rows([Deleterow]).EntireRow.Delete

So what happens, is when I run my module via VBA, everything takes place as expected. If, I run it through the button that is designed to perform this task, the deletion doesn't happen (or happens and is overwritten perhaps - no way of testing)...

Does anyone have any ideas?

Thank you kindly!

[mdmackillop]

Should Mycase be string as shown or should it be a variable. If that is correct try

Dim Deleterow As Long
On Error Resume Next
Deleterow = Application.WorksheetFunction.Match("Mycase", Worksheets("Sheet3").Range("A:A"), 0)
If Deleterow <> 0 Then Rows([Deleterow]).EntireRow.Delete