Excel/decreasing list VBA
I received your reply and your suggestion works in a new work book using the same cell and sheet numbers given in the directions; however, I already have a huge workbook that I've put a lot of work into and changing my data to the same cells as the directions would cause me hours of work. What part of the VBA code would I need to change if my list was called "Names" and in cells F2:F24 in sheet2 (named "Data Validation") and how would I get the list to not show blank cells (there is a "ignore blank cells" function but it doesn't seem to make a difference whether its checked or not. I tried editing the code myself but it isn't very intuitive and my changes don't work.
**put his in the worksheet that contains the data validation dropdown.** The defined range "Names" needs to already exist when this code is first run.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strVal As String
Dim strEntry As String
Dim sh As Worksheet, r As Range
Set r = ThisWorkbook.Names("Names").RefersToRange
Set sh = r.Parent
on Error Resume Next
strVal = Target.Validation.Formula1
If Not strVal = vbNullString Then
strEntry = Target
Application.EnableEvents = False
.Replace What:=strEntry, Replacement:="", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
r.Sort Key1:=r(1), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
r.Resize(r.Rows.Count - 1, 1).Name = "Names"
Application.EnableEvents = True
On Error GoTo 0
It worked for me in Windows. It isn't as dynamic in determining the new extent of names. It assumes that one name has been eliminated when it runs.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here