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?

 

[Akuini]

Thanks Akuini,

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.

Oops, sorry, I made a mistake:
Change this:

 ActiveSheet.Range(Cells(rc.Row, rc.Column), Cells(rr, "N")).AutoFilter Field:=rc.Column, Criteria1:=Me.ComboBox1.Value

to this:

ActiveSheet.Range(Cells(rc.Row, "A"), Cells(rr, "N")).AutoFilter Field:=rc.Column, Criteria1:=Me.ComboBox1.Value

No need to change 'SearchOrder:=xlByColumns', it has nothing to with that.

 

[dboone25]

Ok thanks,

Still having trouble understanding this:

If I paste the data into any row or column for example 'Row 26' ; 'Column F' and apply the AutoFilter from:

[COLOR=#333333]ActiveSheet.Range(Cells(rc.Row, "A"), Cells(rr, "N")).AutoFilter Field:=rc.Column, Criteria1:=Me.ComboBox1.Value[/COLOR]

The ComboBox1 selects 'Row 26' ; 'Column K' where it should select 'Column F' that was why I was asking if you have to add the SearchOrder:=xlByColumns as well as the Searchorder:=xlByRows

 

[Akuini]

Ok thanks,

Still having trouble understanding this:

If I paste the data into any row or column for example 'Row 26' ; 'Column F' and apply the AutoFilter from:

[COLOR=#333333]ActiveSheet.Range(Cells(rc.Row, "A"), Cells(rr, "N")).AutoFilter Field:=rc.Column, Criteria1:=Me.ComboBox1.Value[/COLOR]

The ComboBox1 selects 'Row 26' ; 'Column K' where it should select 'Column F' that was why I was asking if you have to add the SearchOrder:=xlByColumns as well as the Searchorder:=xlByRows

Not sure why that happen.
I give you an example:
Using this code:

    Set rc = Cells.Find("xx", LookAt:=xlWhole, [COLOR=#0000cd]SearchOrder:=xlByRows[/COLOR], 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

On this data:

	A	B	C
1	1	1	1
2	2	2	xx
3	3	3	3
4	xx	4	4
5	5	5	5

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

The code will find rc at C2, because it search from row1 then row2 then row3 and so on
If we change the code to SearchOrder:=xlByColumns

    Set rc = Cells.Find("xx", LookAt:=xlWhole, [COLOR=#0000cd]SearchOrder:=xlByColumns[/COLOR], 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

The code will find rc at A4, because it search from col A then col B then col C and so on

 

[Akuini]

Sorry, I should say "The code will find xx at .."

 

[dboone25]

You have nailed it, that makes more sense now.

End of the week and brain has stopped working.

Many thanks again

 

[dboone25]

This works great and test it out. Only problem with the ComboBox1 is that you have to select the data twice for it to show in the combobox.

Meaning ComboBox1 holds the data for the selected column 1, 2, 3, 4 and so on....if I select 1 it wont appear in the ComboBox but if I select it again it seems to work.

It seemed to work ok before the small changes, would I need to include anything?

.....Update...

Found it to be the event Enter() section:

I was using

'Private Sub ComboBoxBox1_Enter()'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.ComboBox1Box.List = d.Keys
'
'End Sub

instead went back to the old code

Private Sub ComboBox1_Enter()

'clear contents and update combobox if worksheet has been updated or data has been removed
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

Seems to work ok, not sure why.

 

[Akuini]

This works great and test it out. Only problem with the ComboBox1 is that you have to select the data twice for it to show in the combobox.

Meaning ComboBox1 holds the data for the selected column 1, 2, 3, 4 and so on....if I select 1 it wont appear in the ComboBox but if I select it again it seems to work.

It seemed to work ok before the small changes, would I need to include anything?

That's a bit strange, not sure why. You are talking about one combobox, right? Not about 2 combobox that if 1 combobox change then the other will change accordingly.

 

[dboone25]

That's a bit strange, not sure why. You are talking about one combobox, right? Not about 2 combobox that if 1 combobox change then the other will change accordingly.

The update function still does not clear the ComboBox when applying the autofilter,

Meaning if I select a range of data the other comboboxes still show the data in the drop down section e.g


Data Set:


A

 

[dboone25]

That's a bit strange, not sure why. You are talking about one combobox, right? Not about 2 combobox that if 1 combobox change then the other will change accordingly.

The update function still does not clear the ComboBox when applying the autofilter,

Meaning if I select a range of data the other comboboxes still show the data in the drop down section e.g


Data Set:


A B C

1 Y U
2 E B


If I apply the filter to ComboBox1 and select '1' the data in Combobox2 shows data 'Y' and 'E' and Combobox3 shows data 'U' and 'B' instead of only showing the first row.

The code for the Enter() event is:

Private Sub ComboBox1_Enter()    'clear contents and update combobox if worksheet has been updated or data has been removed
Dim vlist, r As Range, i As Long, rc As Range


 Set d = CreateObject("scripting.dictionary")
With ActiveSheet
  Set rc = .Rows(1).Find("xxxxx", 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


Have I placed the code in the right event?