PDA

View Full Version : AdvancedFilter result range EXCLUDING input column title



S3RG3
07-04-2017, 11:00 PM
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("D18:D19"), 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
07-05-2017, 12:36 AM
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
07-05-2017, 08:03 AM
In Excel 2016, doing the operation manually I get an error that says "You can only copy filtered data to the active sheet"

mdmackillop
07-05-2017, 08:38 AM
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
07-05-2017, 11:33 PM
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
07-06-2017, 03:26 AM
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