autofilter returns blank cells

hitch_hiker

Active Member
Joined
Feb 21, 2012
Messages
291
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

VBA Code:
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,765
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Feb 21, 2012
Messages
291
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

1625029922162.png
this is what I start with
1625029958639.png

this is what I expect
1625029990358.png
this is what I get
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,765
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Feb 21, 2012
Messages
291

ADVERTISEMENT

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
VBA Code:
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

Well-known Member
Joined
Feb 23, 2021
Messages
1,765
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
    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

Active Member
Joined
Feb 21, 2012
Messages
291
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.
VBA Code:
    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

Well-known Member
Joined
Feb 23, 2021
Messages
1,765
Office Version
  1. 365
Platform
  1. Windows
No problem. Happy to help.
Having the pictures made it much easier.
 

Forum statistics

Threads
1,141,864
Messages
5,709,066
Members
421,611
Latest member
Lisa W

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top