How to delete unselected columns from range

Question

I am new to VBA and am trying to delete unwanted columns loaded from a .csv file. I am importing a large amount of data but then I ask the user what columns they want to keep going by "ID num.". There are a lot of columns with different ID no. and I want to ask the user what they want to keep and delete the rest.

The problem is I need to delete all the other columns the user didn't want but I still need to keep the first 6 columns and the last two columns as that is different information.

Here is what I have so far:

Sub Select()
'the below will take the users inputs
UserValue = InputBox("Give the ID no. to keep seperating with a comma e.g"12,13,14")
'the below will pass the user inputs to the example to split the values
Call Example(UserValue)
End Sub

Sub Example(UserValue)


TestColArray() = Split(UserValue, ",")
 For Each TestCol In TestColArray()
' keep all the columns user wants the delete the rest except the first 6 columns and last 2
Next TestCol

End Sub

That is what I have so far, it is not much but the user could put in a lot of columns with different ID number in the input box the way the Excel sheet is laid out all the ID no.s are in row 2 and the first 6 and last 2 columns are blank of row 2 since the ID no. does not apply. I hope that helps.

Answer 1

try this (commented) code:

Option Explicit '<--| use this statament: at the cost of having to declare all used variable, your code will be much easier to debug and maintainable

Sub MySelect()
    Dim UserValue As String

    'the below will take the users inputs
    UserValue = Application.InputBox("Give the ID no. to keep seperating with a comma e.g: ""12,13,14""", Type:=2) '<--| use Type:=2 to force a string input

    'the below will pass the user inputs to the example to split the values
    Example UserValue '<--| syntax 'Call Example(UserValue)' is old
End Sub

Sub Example(UserValue As String)
    Dim TestCol As Variant
    Dim cellsToKeep As String
    Dim firstIDRng As Range, lastIDRng As Range, IDRng As Range, f As Range

    Set firstIDRng = Range("A2").End(xlToRight) '<-- first ID cell
    Set lastIDRng = Cells(2, Columns.Count).End(xlToLeft) '<-- last ID cell
    Set IDRng = Range(firstIDRng, lastIDRng) '<--| IDs range
    cellsToKeep = firstIDRng.Offset(, -6).Resize(, 6).Address(False, False) & "," '<--| initialize cells-to-keep addresses list with the first six  blank cells at the left of first ID
    For Each TestCol In Split(Replace(UserValue, " ", ""), ",") '<--| loop through passed ID's
        Set f = IDRng.Find(what:=TestCol, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) '<--| search for the current passed IDs range
        If Not f Is Nothing Then cellsToKeep = cellsToKeep & f.Address(False, False) & ","  '<--| if the current ID is found then update cells-to-keep addresses list
    Next TestCol
    cellsToKeep = cellsToKeep & lastIDRng.Offset(, 1).Resize(, 2).Address(False, False) '<--| finish cells-to-keep addresses list with the firts two blank cells at the right of last ID

    Range(cellsToKeep).EntireColumn.Hidden = True '<-- hide columns-to-keep
    ActiveSheet.UsedRange.EntireColumn.SpecialCells(xlCellTypeVisible).EntireColumn.Delete '<--| delete only visible rows
    ActiveSheet.UsedRange.EntireColumn.Hidden = False '<-- unhide columns
End Sub

it's assumed to be working with currently active worksheet

Answer 2

A simple google search produces this. On the first page of results too. Perhaps this will suit your needs.

If the data set that needs to be deleted is really large (larger than the ranges you want to keep too.) Then perhaps only select the columns you want to have whilst you import the csv? This stackoverflow question shows how to import specific columns.

EDIT: So from what I believe the OP is stating as the problem, there is a large csv file that is being imported into excel. After importing there is alot of redundant columns that should be deleted. My first thought would be to only import the needed data (columns) in the first place. This is possible via VBA by using the .TextToColumns method with the FieldInfo argument. As stated above, the stackoverflow question linked above provides a means of doing so.

If the selective importing is not an option, and you are still keen on making an inverse of the user selection. One option would be to create 2 ranges (one being the user selected Ranges and the second being the entire sheet), you could perform an intersect check between the two ranges and delete the range if there is no intersection present (ie. delete any cell that is not part of the users selection). This method is provided by the first link I supplied and is quite straight forward.