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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,714
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Rich (BB code):
.AutoFilter Field:=5, Criteria1:=">=" & from, Operator:=xlAnd, Criteria2:="<=" & fromt & "ZZ"
 
Solution

UweNaujak

New Member
Joined
Jan 13, 2021
Messages
9
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thank you so much, works perfect now. What does that "ZZ" actually stand for or what is the logic behind it?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,714
Office Version
  1. 365
Platform
  1. Windows
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)
 

UweNaujak

New Member
Joined
Jan 13, 2021
Messages
9
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,714
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Forum statistics

Threads
1,143,835
Messages
5,721,076
Members
422,339
Latest member
SHIVATVM

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