VBA Autofilter a text column by a range of beginning letters

UweNaujak

New Member
Joined
Jan 13, 2021
Messages
9
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
I am hoping someone can help me with this. I have a Listing of Names (last name is in Column E = Field #5).

Example of my problem:

When looking for a range of names starting with the letter “A” to “C”, user is prompted to enter the 1st letter (A) and the last letter (C), A is entered into inputbox “from”, c into “fromt”. Running the code shown below, it only gives me names starting with A and B but omits C. If I want all names including C I have to enter D into “fromt”. Given my code, this does not make sense to me. What am I doing wrong?

VBA Code:
Dim from As String, fromt As String

from = InputBox("From which 1st letter of Last Name", )

fromt = InputBox("To which 1st letter of Last Name ", )

With Workbooks("DMS Donors.xlsm").Sheets("Donors").Range("A1").CurrentRegion

.AutoFilter Field:=5, Criteria1:=">=" & from, Operator:=xlAnd, Criteria2:="<=" & fromt

End With

With ActiveSheet.AutoFilter.Range

.Copy Worksheets("DonorSearch").Range("A1")

End With

Workbooks("DMS Donors.xlsm").Sheets("Donors").AutoFilterMode = False

Application.CutCopyMode = 0



Thank you, Uwe
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hi & welcome to MrExcel.
How about
Rich (BB code):
.AutoFilter Field:=5, Criteria1:=">=" & from, Operator:=xlAnd, Criteria2:="<=" & fromt & "ZZ"
 
Upvote 0
Solution
Thank you so much, works perfect now. What does that "ZZ" actually stand for or what is the logic behind it?
 
Upvote 0
If you had somebody with a last name of Cash then that is greater than C as it has an a after the C. So adding the ZZ to the fromt variable you will get CZZ, which should catch all entries starting with C (unless you have any names that start with CZZ)
 
Upvote 0
that makes sense. Thank you so very much for the help, I wasted so much time with this and started having serious doubts about myself. LOL.
Thanks again, have a great day
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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
Back
Top