Excel tutorials

Select, Copy and Export Data from a Cells in the Specific Column and Save the TXT file Without Empty Cells

How to Export Data from the First to the Last Filled Cell in the Column and Save it to a '.txt' file without blank cells

If you've ever had the need to do the action you can read in the titles of this site, then study this Excel example tutorial that using VBA macros you can Select and Copy to Text File, Data from the First Cell to the Last filled cell but without empty cells:

The situation is the same as in the picture below.

The First and Last Filled Cells are within the Range of the Excel Column

Look at the picture below, column 'A'. In the picture, you see the filled cells in the column. For this Excel example I used the cell range of 'A1:A67'. Imagine that there may be up to 10000 rows in the column. Eg. if you use a formula and the results of the formula can fill more or less column rows (depending on the condition you set). Your goal is to select all the cells up to the Last Filled Cell (in this case the last filled cell is 'A67'). Of course, you do not want to select manually, but you want to do it automatically using the VBA.

First and Last filled cells in Column range

 

Selecting From the First to the Last Filled Cell and Copy it to the Clipboard

Given the above picture to select a specific cell range up to the Last Non-Empty Cell (in a specific column) using VBA, use this VBA macro below.

This VBA macro below after starting will select/mark all the cells of 'A1:A67'. Therefore, they will also select empty cells that are between the First Cell Filled up to the Last Filled Cell. If there is a data in cell 'A750', the VBA would then select all from 'A1:A750'. (Note: This VBA macro is not good for Copying Filled Cells Only, in a text file).

Option Explicit
Sub SelectFromFirstToLastFilledCell()
'Select From First Cell to Last Filled Cell in Column A
Dim iTotalRows As Long
Dim iRow As Long
Dim i As Long
'Get total number of rows in sheet
iTotalRows = ActiveSheet.UsedRange.Columns(22).Rows.Count
'Go from last row till first row from back which has data in cell
For i = iTotalRows To 1 Step -1
If Cells(i, 1).Value <> "" Then 'Number 1 is Column A
iRow = i
Exit For
End If
Next
'Make a copy of range
ActiveSheet.Range("A1:A" & iRow).Copy 'concatenate A1 and Row of Last Filled Cell and copy to Clipboard
End Sub

So, after running a VBA macro Excel will be Select and Copy to the Clipboard all the cells in column 'A' to the Last Filled Cell. You can then decide what to do with copied data. Eg. Open 'Notepad' program and paste all of the data from the Clipboard and then SaveAs in a Text file in a specific Folder.

Result after running a VBA macro look at the picture below. Notice in the picture below that this VBA macro has selected all in the range of 'A1:A67'.

Selected column range from first to last filled cell

 

How to Copy Data to Clipboard From the First to the Last Filled Cell in Column 'A' Save in a TXT File

This second Excel example is similar to the previous example. In the VBA macro below, I added certain lines of the program code, that will automatically open 'Notepad' and you need to decide where to Save the TXT file.

Option Explicit
Sub ExportAndSaveToTxtWithoutEmptyCells()
'Select And Copy to the Notepad, From First Filled Cell to the Last Filled Cell
Dim iTotalRows As Long
Dim iRow As Long
Dim i As Long
'Get total number of rows in sheet
iTotalRows = ActiveSheet.UsedRange.Columns(22).Rows.Count
'Go from last row till first row from back which has data in cell
For i = iTotalRows To 1 Step -1
If Cells(i, 1).Value <> "" Then 'Number 1 is Column A
iRow = i
Exit For
End If
Next
'Make a copy of range
ActiveSheet.Range("A1:A" & iRow).Copy 'concatenate A1 and Last Filled Cell and copy
'Start Notepad with focus
Shell "notepad.exe", vbNormalFocus
'Send the standard CTRL+V and Paste to the active window Notepad
SendKeys "^V"
SendKeys "^a"
SendKeys "^c"
SendKeys "^{HOME}"
End Sub

Result after running VBA macros above. You opened the 'Notepad' window with the first to last cell data exported from column 'A'. Save a TXT file to a specific folder as desired.

Exported data from excel to notepad and save

 

Export Data from Column from First to Last Non-Empty Cell, Without Empty cells and Save in TXT file

This third Excel example refers to the automatic selection from the first to the last filled cell in the column and the automatic output and Save in the TXT file in a specific folder. The situation is the same as at the beginning of this tutorial. In this case, use the VBA macro below.

'Option Explicit
Sub ExportToTxtWithoutBlankCells()
'Select And Copy From First Cell to Last Filled Cell Without Empty Cell
Set x = Application.InputBox("Push CTRL key and select Column letter", "Export", , , , , , 8)
Set DataToExport = Intersect(Range("1:500"), x.EntireColumn) 'set Max. range to last row
'delimiter = " ; "
delimiter = vbTab & ";" & vbTab
exportstr = ""
For Each rw In DataToExport.Columns(1).Cells
If Len(rw.Text) > 0 Then
i = 0
For Each cll In Intersect(Rows(rw.Row), DataToExport).Cells
If i = 0 Then exportstr = exportstr & cll.Text Else exportstr = exportstr & delimiter & cll.Text
i = i + 1
Next cll
exportstr = exportstr & vbCrLf
End If
Next rw
exportstr = Left(exportstr, Len(exportstr) - 2)
For Each are In DataToExport.Areas
For Each colm In are.Columns
myName = myName & Split(colm.Address, "$")(1)
Next colm
Next are
ff = FreeFile
Open "C:\Temp\ExportedFile " & myName & ".txt" For Output As #ff 'change path C:\Temp\ExportedFile to your path
Print #ff, exportstr;
Close #ff
End Sub

Notice in the VBA macro code above (bold letters) that you need to set the path to the destination folder in which Excel will automatically export Data and Save TXT file.

After you start VBA macro above you will see a 'dialog box' where you need to set the column from which you want to export the data to the tekstual file. You can type this column manually in the field, in the form '$A:$A' or press the CTRL key and click on the column 'A'. It looks like the picture below.

Selecting Column for Export Data

After clicking the 'OK' button you need to open the destination folder in which the text file was saved. It looks like this in the picture below.

Destination and folder saved exported txt file

When you open the exported TXT file you have a situation like in the picture below. Notice in the image below that there are No Empty cells in the text file that are in the Excel Workbook. This is very important if you have several thousand rows and the Last Filled Cell can be at the end of the cell range in the column.

Exported data without blank cells in txt file

This is the Excel example tutorial step-by-step, selecting to the Last Filled Cell, copying the selected area, and exporting to the TXT file in a specific folder, and all the merit goes to the VBA makers who have published VBA macros through various forums (source vbaexpress.com, elitesecurity.org).

Conclusion:
The basic difference between the second and third VBA macros of this Excel tutorial is the following

  • The second VBA macro exports all cells in the range up to the last filled cell. So it also includes empty cells that are between the first and the last filled cell. In addition, you must manually Save the TXT file in a specific folder.
  • The third VBA macro exports only the filled cells and automatically Save the exported file to the specified folder.

Some peoples on the forums have asked me why I do not set an example Excel file from my tutorials. I think you'll learn best if you create a Workbook with your own data and follow this Excel tutorial step by step. Still here for Download ZIP file Copy Data From the First to the Last Filled Cell and Save the TXT file, according to which I created this Excel tutorial.

If you want to run the Excel file format *.xls or *.xlsm and you are unsure whether the file contains the malicious VBA code, then press the SHIFT key when opening the Workbook (In this way you will prevent the execution of VBA macros in start).