Selected Answer
Formatting doesn't remove data. It only displays them differently. The problem either hails from when the CSV file is created or when it is converted to Excel. Open the CSV file in a text editor. Look at the end, if there are blank lines, probably represented by commas or zeroes or even blanks. For good measure also look at the end of the lines to see if blank columns are added. Each column is represented by a comma.
If the CSV file contains only data, as it should, the fault must be with the process of importing it into Excel.. Somehow Excel would get the notion that it must fill up all of its cells. Note that this might be true horizontally as well as vertically. Closely look at the dialog box that opens when you import the CSV file, in particular the separators that are specified. A CSV file is comma-separated. The effect you observe may be caused by specifying additional separators. I lack the experience to do more than guess but it appears plausible to me that there should be a setting which suppresses the creation of blank cells such as you experience, and that setting must be in the import dialog.
If all of this fails here is code to remove the blanks after they were created. You will see that the circumstances for which this code was created match the first scenario in that the blanks are physically present in the CSV file. Frankly, if that were so in your case I would suggest to look at what can be done to the TXT file rather than dealing with the problem after import. You can edit it using NotePad or VBA. However, as a stop-gap the code might be useful for you.
Please try this code instead of the system you have been using. Install it in a standard module of your workbook and run it after you have updated the Data sheet.
Sub GetColumn()
' 14 Apr 2018
Const InputTabName As String = "Data"
Const OutputTabName As String = "Filtered"
Const ColumnList As String = "C, F:G, L, V:Z"
Dim CopyRange As Range
Dim Rng As Range
Dim Sp() As String
Dim Clm As Variant
Dim Rf As Long, Rl As Long ' first row, last row
Dim C As Long
Sp = Split(ColumnList, ",")
With Worksheets(InputTabName)
With .UsedRange
Rf = .Row
Rl = Rf + .Rows.Count - 1
End With
For C = 0 To UBound(Sp)
Clm = Split(Sp(C), ":")
If UBound(Clm) = 0 Then
ReDim Preserve Clm(1)
Clm(1) = Clm(0)
End If
Clm(0) = Columns(Trim(Clm(0))).Column
Clm(1) = Columns(Trim(Clm(1))).Column
Set Rng = Range(.Cells(Rf, CLng(Clm(0))), .Cells(Rl, CLng(Clm(1))))
If C Then
Set CopyRange = Application.Union(CopyRange, Rng)
Else
Set CopyRange = Rng
End If
Next C
End With
CopyRange.Copy Destination:=Worksheets(OutputTabName).Cells(1, 1)
End Sub
It's a no-frills macro. Before use, please make sure that the
Filtered sheet is blank. The names of the
Input and
Output tabs must be spelled correctly. Use the constant
ColumnList to specify the columns you wish to lift. Follow the format (comma separated) of the example. To run the code place the cursor anywhere in the code and press F5 (or
Run > Run Macro).
To install the code, press Alt+F11 to open the VB Editor. Right-click on the workbook's name which you see in the Project Explorer window on the top left. Select Insert and Module. Paste the code in the blank pane on the right of your screen.
