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.
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
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?
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?