Named ranges and deleting columns to update worksheet

dboone25

Board Regular
Joined
May 8, 2015
Messages
185
Hello.

Little stuck on how to go about deleting columns and updating the worksheet. I have been looking at named ranges but so far this does not work.
This part of my code which runs in module1 opens a dialog box to select a .csv file, opens into a new workbook and places the delimited data into A1.

Code:
' Declare variables.
Dim myFile As String
Dim FileName As Variant
Dim myUserForm As FSelectFilter
Dim Rng As Object
myFile = "CSV Files (*.csv),*.csv"
'Title = "Select .CSV File to Import"
FileName = Application.GetOpenFilename(Title:="Select .csv File to Import")
' Select and open the dialog box.
' Set delimiters to Sheet1
Workbooks.OpenText FileName:=FileName, DataType:=xlDelimited, Comma:=True, Local:=True

I have a userform that has the autofilter function refrencing each corresonding cell that the user can select the data from a combobox and apply the filter with a command button ( many thanks to Aukuini for your help with this).

This example is for column G filter button

Code:
Private Sub AcqRefButton_Click()
    'search for data in column G
    Dim rr As Long
    rr = Range("G" & Rows.Count).End(xlUp).Row
    'clear autofilter before using autofilter
    'ActiveSheet.Range("$A$1:$N$" & rr).AutoFilter
    ActiveSheet.Range("$A$1:$N$" & rr).AutoFilter Field:=7, Criteria1:=Me.AcqRefBox.Value
End Sub

Private Sub UserForm_Initialize()
'different list for AcqRef combobox
    vlist = Range("G2", Cells(Rows.Count, "G").End(xlUp)).Value
    Set d = CreateObject("scripting.dictionary")
    For i = LBound(vlist) To UBound(vlist)
    d(vlist(i, 1)) = 1
    Next
    Me.AcqRefBox.List = d.Keys
End Sub

In my case is it best to used named ranges for each cell refrence that when a user does delete a column the refrences will refer to each combobox ie acq_ref with refer to the data in that column regardless of other columns being deleted? Would I need to place this in the initilize event or the event module? Appreciate any help with this?
 
One quick question just to confirm.

When you close and reopen the UserForm the ComboBox updates with the information from the selected column.

I have a 'Reset' Command button that clears the ComboBox:

Code:
ComboBox1.Value = Null

How can I add say an update function to that command button? If not then Ill leave it, thanks :)
 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
I'm not sure what you want.
Your code above only remove the text in the combobox, but the list is still there. So what do you mean by 'update function'? update what?
 
Upvote 0
Update meaning if there have been any changes to the data, data has been deleted from the worksheet....
 
Upvote 0
Update meaning if there have been any changes to the data, data has been deleted from the worksheet....

you mean if data has been deleted from the worksheet then you want to update the combobox list?
 
Upvote 0
Yes thats correct.

So if data in column1 combobox1 is ...'1','2','3','4','5' and i delete '2' the combobox will show '1','3','4','5'
 
Upvote 0
Ok let's say you use the code to populate the combobox1 list in 'Private Sub UserForm_Initialize()', right?
You can copy the code inside it & paste it to 'Private Sub ComboBox1_Enter()'
So, every time you enter combobox1, it will populate/update the list.
 
Upvote 0
Yup, that makes sense! originally I was pasting it into the Combobox1_Change() event but you have saved me again!

Thanks
 
Upvote 0
Need to include one last thing...

The function SearchOrder:=ByColumns in the command "filter" button

Code:
Dim rr As Long, rc As Range
    Set rc = Rows(1).Find("tail_no", LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    rr = Cells(Rows.Count, rc.Column).End(xlUp).Row
    ActiveSheet.Range("$A$1:$N$" & rr).AutoFilter Field:=rc.Column, Criteria1:=Me.ComboBox1.Value

Can I add the function Searchorder:=ByRows into Set rc? so the final code will look like:

Code:
Dim rr As Long, rc As Range

    Set rc = Rows(1).Find("xxx", LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
    Set rc = Rows(1).Find("xxx", LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    rr = Cells(Rows.Count, rc.Column).End(xlUp).Row
    ActiveSheet.Range("$A$1:$N$" & rr).AutoFilter Field:=rc.Column, Criteria1:=Me.ComboBox1.Value

The reason why Im asking about adding in this is depending on where the user displays the data so by adding in the xlByRows this will allow the UserForm to search for the column regardless on what row it has been started.

Im guessing the initliaize event would have to be changed as well:


Code:
Private Sub UserForm_Initialize()    
    Dim vlist, r As Range, i As Long, rc As Range
    
   
    Set d = CreateObject("scripting.dictionary")
With ActiveSheet
  Set rc = .Rows(1).Find("xxx", LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
  If Not rc Is Nothing Then
     For Each r In .Range(.Cells(2, rc.Column), .Cells(Rows.Count, rc.Column).End(xlUp))
      d(r.Value) = 1
     Next
  Else
  Exit Sub
  End If
End With
    Me.ComboBox1.List = d.Keys


End Sub
 
Last edited:
Upvote 0
If the header row is dynamic, then the code would be something like this:
Code:
Sub yyy()
Dim rr As Long, rc As Range

    Set rc = Cells.Find("xxx", LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    rr = Cells(Rows.Count, rc.Column).End(xlUp).Row 
    ActiveSheet.Range(Cells(rc.Row, rc.Column), Cells(rr, "N")).AutoFilter Field:=rc.Column, Criteria1:=Me.ComboBox1.Value
End Sub


Private Sub UserForm_Initialize()
    Dim r As Range, i As Long, rc As Range, d As Object, rr As Long
    
    Set d = CreateObject("scripting.dictionary")
    Set rc = Cells.Find("xxx", LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    rr = Cells(Rows.Count, rc.Column).End(xlUp).Row

  If Not rc Is Nothing Then
     For Each r In Range(Cells(rc.Row + 1, rc.Column), Cells(rr, rc.Column))
      d(r.Value) = 1
     Next
  Else
  Exit Sub
  End If

    Me.ComboBox1.List = d.Keys

End Sub

this line:
Code:
Set rc = Cells.Find("xxx", LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
will search "xxx" in the activesheet (not just in row 1) starting from cell A1 row by row.
If say "xxx" is in cell F3 then:
rc.column = 6
rc.row = 3
 
Upvote 0
Thanks Akuini,

Just to confirm the SearchOrder function, do you need to include
Code:
[COLOR=#333333]SearchOrder:=xlByColumns[/COLOR]

So this will include searching for 'xxx' in any row or any column.


Reason why I ask as running a test by pasting the data into any Row or Column when I apply the 'filter' from ComboBox1 in the CommandButton_Click() event the filter is not applied to 'xxx' instead its applied to a different column of data.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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