autofilter returns blank cells

[hitch_hiker]

I am trying to do a autofilter using an input box. the sheet has filters assigned to each column and one column is "permamently" set at only show blanks.
this is a invoice register filtered by blank in the paid column, this gives me only invoices which have not been paid. The macro is written by using the macro recorder and taking the working section and pasting into the working work in progress, so the range is set to a1 to p11853 the results I get start at row 11843 which has no data in it.
My intention is to get a list of "customer A" with unpaid invoices regardless of age and copy them into a formatted sheet to act as a statement. Then "customer B", "customer C" etc
I have seen comments re removing the pre-existing filters first, this is not the preferred option, I use the sheet with the filters on every day and I have changed the range to c1:c11853 with no effect. Any suggestions for how to move ahead with this.
I test the code at each line and only go to the next line (or group of lines) once it works. I am also rem ing the hell out of it so I can follow it later on

Sub createStatement()
'
' CreateStatement Macro
'
' Keyboard Shortcut: Ctrl+j
'
Rem FROM SHEET "inv reg"

    Dim trade As String ' customer name for search, comes from inputbox

Rem rename workbook as "invoice sample test", accept duplicate name warning
Application.DisplayAlerts = False 'disable "file already exists"
ActiveWorkbook.SaveAs FileName:= _
        "C:   \[PATH]   invoice sample test work in progress.xlsm" _            ' *** [path] replaces the existing path for security
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False '   %%% remove "work in progress" when it all works
Application.DisplayAlerts = True ' re-activate "file already exists"

Rem make sure "inv reg" is the active sheet
 Sheets("inv reg").Select
    Columns("C:C").Select

Rem SEARCH FOR (ENTERED SEARCH CRITERIA as user input)
    trade = InputBox("enter customer name or part name the more unique the better", "", "Type Here") ' customer name to search
ActiveSheet.Range("c1:c118542").AutoFilter Field:=3, Criteria1:=trade

 

[Alex Blakenburg]

Can you post an image of what you are getting and what you expect to be getting. Also what you are typing into the input box ?
If you can give an XL2BB of some of the data, that would be helpful as well.

I don't think this is causing you problem but you refer to 5 digit row numbers as being the bottom row but your sample code goes to 6 digits ie c118542

 

[hitch_hiker]

Can you post an image of what you are getting and what you expect to be getting. Also what you are typing into the input box ?
If you can give an XL2BB of some of the data, that would be helpful as well.

I don't think this is causing you problem but you refer to 5 digit row numbers as being the bottom row but your sample code goes to 6 digits ie c118542

Thanks Alex,
Just trying to get that info now, being end of Australian financial year just slows things down. the row number was a typo it is c118542, I am trying to get the autofilter to filter based on the input box, however, typing in "carpenters" ( or any actual name ) results in the same thing
I am trying to create a screen shot, but, I'm running into problems trying to download zl2bb, I'll send the sheet trough soon

this is what I start with

this is what I expect

this is what I get

 

[Alex Blakenburg]

Can you confirm that when you typed in carpenters that you actually typed in an asterisk at the end eg carpenters*
PS: I am also in Australia, so we are in the same time zone

 

[hitch_hiker]

Can you post an image of what you are getting and what you expect to be getting. Also what you are typing into the input box ?
If you can give an XL2BB of some of the data, that would be helpful as well.

I don't think this is causing you problem but you refer to 5 digit row numbers as being the bottom row but your sample code goes to 6 digits ie c118542

Can you confirm that when you typed in carpenters that you actually typed in an asterisk at the end eg carpenters*
PS: I am also in Australia, so we are in the same time zone

Thanks Alex,
I get the anticipated result if I type "carp" or "carpen" or any other iteration I did not type in a following asterisk. I tried the macro and typed in "carpenters*" and it gave the anticipated results. So the problem is it needs an asterisk, any advise to the syntax to include that in the macro

trade = InputBox("enter customer name or part name the more unique the better", "", "Type Here") ' customer name to search
ActiveSheet.Range("a1:p118542").AutoFilter Field:=3, Criteria1:=trade

so it accepts carpenters and adds the "*"

 

[Alex Blakenburg]

Sure but if you add the asterisk(s) it increases the scope of the filter so you may be opening yourself up to get too many results.

    trade = trade & "*"             ' Begins with
    trade = "*" & trade & "*"   ' Contains

PS: FYI in VBA common practice is to use a single quote mark ' rather than the Rem word

 

[hitch_hiker]

Sure but if you add the asterisk(s) it increases the scope of the filter so you may be opening yourself up to get too many results.

    trade = trade & "*"             ' Begins with
    trade = "*" & trade & "*"   ' Contains

PS: FYI in VBA common practice is to use a single quote mark ' rather than the Rem word

thanks Alex,
I use rem to show my logic step and ' as a programing comment . I started with a macro that used to work then stopped, So I wanted to copy ideas from it, then change to " ' " once it works
... Thank you very much. By you asking about the asterisk, it solved the problem

 

[Alex Blakenburg]

No problem. Happy to help.
Having the pictures made it much easier.