Named ranges and deleting columns to update worksheet

[dboone25]

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.

' 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

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?

 

[dboone25]

What about adding say a command buttong to update the UserForm from the worksheet? When a user deletes any column of data then the 'Update button will change the cell references, can this be done?

 

[Akuini]

What about adding say a command buttong to update the UserForm from the worksheet? When a user deletes any column of data then the 'Update button will change the cell references, can this be done?

Is column H has a header & the header is unique (no other header has the same name)?
If yes then it can be done.

 

[dboone25]

Yes column h has a header and is unique as in no header has the same name.

Do you know how this can be done?

 

[Akuini]

Yes column h has a header and is unique as in no header has the same name.

Do you know how this can be done?

OK:
Assuming the header is in row 1
Notes:
change "sheet1" to suit
change "xx" to your header name

Private Sub UserForm_Initialize()

Dim vlist, r As Range, i As Long, rc As Range

Set d = CreateObject("scripting.dictionary")
With Sheets("[COLOR=#b22222]Sheet1[/COLOR]")
  Set rc = .Rows(1).Find("[COLOR=#b22222]xx[/COLOR]", LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
  If Not rc Is Nothing Then
     For Each r In .Range(.Cells(1, 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

 

[dboone25]

OK:
Assuming the header is in row 1
Notes:
change "sheet1" to suit
change "xx" to your header name

Private Sub UserForm_Initialize()

Dim vlist, r As Range, i As Long, rc As Range

Set d = CreateObject("scripting.dictionary")
With Sheets("[COLOR=#b22222]Sheet1[/COLOR]")
  Set rc = .Rows(1).Find("[COLOR=#b22222]xx[/COLOR]", LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
  If Not rc Is Nothing Then
     For Each r In .Range(.Cells(1, 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

Thanks but Im having problems with this. The autofilter still works for column H which is fine but when I delete any column say columnD the autofilter still only selects column H.

 

[Akuini]

Thanks but Im having problems with this. The autofilter still works for column H which is fine but when I delete any column say columnD the autofilter still only selects column H.

So, the combobox works as you need now?
Which one is the code for autofilter?

 

[dboone25]

Yes that code works but it includes the heading in the dropdown list in the ComboBox. Can the heading be excluded from the drop down list?

The code for the autofilter is:

Private Sub AcqRegRefButton_Click()

    'search for data in column H
    Dim rr As Long
    rr = Range("H" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("$A$1:$N$" & rr).AutoFilter Field:=8, Criteria1:=Me.ComboBox1.Value
    
    
End Sub

 

[Akuini]

Yes that code works but it includes the heading in the dropdown list in the ComboBox. Can the heading be excluded from the drop down list?

change:
For Each r In .Range(.Cells(1, rc.Column), .Cells(Rows.Count, rc.Column).End(xlUp))
to:
For Each r In .Range(.Cells(2, rc.Column), .Cells(Rows.Count, rc.Column).End(xlUp))

And try this:

Private Sub AcqRegRefButton_Click()

    'search for data in column H
    Dim rr As Long, rc As Range
    Set rc = Rows(1).Find("xx", 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
   
    
End Sub

 

[dboone25]

YES!! brilliant work! Everything works.

You have been very patient, thank you so much.

I shall apply this to the rest of the ComboBoxes...

 

[Akuini]

YES!! brilliant work! Everything works.

You have been very patient, thank you so much.

I shall apply this to the rest of the ComboBoxes...

You're welcome, glad to help.