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]

Adding a Dynamic Range seems to be a good idea but not sure how to add this.

 

[dboone25]

I have added a named range to column H called acq_reg_ref.

Dim rng1 As Range



Set rng1 = ActiveSheet.Range("H:H")
    ActiveWorkbook.Names.Add Name:="acq_reg_ref", RefersTo:=rng1

But when I change the range name from the combobox it gives me an error.

vlist = Range("H2", Cells(Rows.Count, "H").End(xlUp)).Value    Set d = CreateObject("scripting.dictionary")
    For i = LBound(vlist) To UBound(vlist)
    d(vlist(i, 1)) = 1
    Next
    Me.AcqRegRefBox.List = d.Keys

This includes when I change the name range in the command button

Dim rr As Long    rr = Range("H" & 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:=8, Criteria1:=Me.AcqRegRefBox.Value

Both errors give type miss match. Is this down to

ActiveSheet.Range("$A$1:$N$" & rr)

as the other ranges have not been named or am I missing something out completely?

Any help would be much appreciated.

 

[dboone25]

Ok Got the Named Ranges working working with:

Dim rng1 As Range

Set rng1 = ActiveSheet.Range("H:H")
ActiveWorkbook.Names.Add Name:="acq_reg_ref", RefersTo:=rng1

I have changed 'H' for 'acq_reg_ref' in the UserForm_Initialize event but still does not work when columns are deleted:

vlist = Range("H2", Cells(Rows.Count, "H").End(xlUp)).Value    Set d = CreateObject("scripting.dictionary")
    For i = LBound(vlist) To UBound(vlist)
    d(vlist(i, 1)) = 1
    Next
    Me.AcqRegRefBox.List = d.Keys

How do you update VBA cell references when you delete columns?

 

[Akuini]

Hi, dboone
I don't quite understand what you are trying to do.
Maybe you can explain in more detail.

I have changed 'H' for 'acq_reg_ref' in the UserForm_Initialize event but still does not work when columns are deleted:

How do you update <acronym title="visual basic for applications">VBA</acronym> cell references when you delete columns?

I don't see any code to delete column. Which column is deleted? what do you expect when a column is deleted?

How many combobox do you have? What are their names?
Some notes:
Your code to populate combobox list using dictionary object is intended to get only unique values from a range. If you want to get all value (meaning there could be duplicate) you can use a simpler methods:

With Sheets("Sheet1")
Me.ComboBox1.List = .Range("A1", .Cells(Rows.Count, "A").End(xlUp)).Value
End With

Your code & the above code are actually for a dynamic range, so I don't understand why you created a named range.

 

[dboone25]

Hi, think im confusing myself. Im trying to reference each column as when I delete any column the combobox will still refer to the correct data, does that make sense?

So data in ComboBox8 refers to the data in column H. If I delete columns A B C then I can still use ComboBox 8 to refer to the correct corresponding data.

 

[dboone25]

1.Which column is deleted? ...I dont have any code to delete columns, this is for when a user decides to manually delete columns.

2.what do you expect when a column is deleted? That the user can use the UserForm combobox to continue to use the auto filter for the correct corresponding data.

3.How many combobox do you have? I have 8 comboboxes intotal. But for the time Im testing out one first.

4.What are their names? TailNoBox,DateTimeBox,PassbandBox,CIBox,AcqReRefBox,AcqReBox,CompReBox and VibChanRefBox.


I hope this helps.

 

[Akuini]

Ok, let's say the combobox8 populate list from column H. Say the list are x,y,z.
1. User open the userform
2. use combobox8 to the auto filter for the correct corresponding data.
3. user exit userform
4. delete a column which make column H values change
5. User open the userform again
6. you want combobox8 still has a list the same as before i.e x,y,z
Am I understanding it correctly?

 

[dboone25]

Yes that is correct.

(As an example) If ComboBox8 populates list from column H and then I decide to delete the whole of column F then the data that was corresponding to Combobox8 now moves automatically one to the left into column G.

So basically If any column that gets deleted from the left of column H the data that originally corresponds to Combobox8 the autofilter will still continue to filter that data set.
Does that make more sense?

 

[Akuini]

Yes that is correct.

(As an example) If ComboBox8 populates list from column H and then I decide to delete the whole of column F then the data that was corresponding to Combobox8 now moves automatically one to the left into column G.

So basically If any column that gets deleted from the left of column H the data that originally corresponds to Combobox8 the autofilter will still continue to filter that data set.
Does that make more sense?

Well, you can't do that. The combobox will get a new value from col H every time userform is initialized.
I suggest you copy the data from col H to different sheet and then populate the combobox list from there.