How to select range of cells, from first non-black to last non-blank cell (VBA)?

Question

I am attempting to export tables from excel workbooks to pipe-delimited txt files which are named in the same manner as the corresponding sheet. The issue is that I am not able to get my macros to iterate through the different sheets in a workbook to export all the non-blank cells to a txt file. Below is my code:

Sub TableExtract()

Dim myFile As String, WS_Count As Integer, x As Integer, rng As Range, cellValue As Variant, i As Integer, j As Integer

WS_Count = ActiveWorkbook.Worksheets.Count
For x = 1 To WS_Count

    myFile = "C:\Users\mohamednuri.beitelma\Desktop\" & ActiveSheet.Name & ".txt"

    Set rng = Sheets(x).Range("A1").CurrentRegion
    Open myFile For Output As #1

    For i = 1 To rng.Rows.Count
        For j = 1 To rng.Columns.Count

            cellValue = rng.Cells(i, j).Value

            If j = rng.Columns.Count Then
                Print #1, cellValue
            Else
                Print #1, cellValue & "|",
            End If

            Next j
        Next i
    Close #1
Next x

End Sub

This code returns an error. Any idea how I can select al lthe content in the range between the first and last non-blank cells, and export it?

Can you show the error you get? – Shukri Gashi May 23 '16 at 11:45 — Shukri Gashi, May 23 '16 at 11:45

score 0 · Accepted Answer · answered May 23 '16 at 11:46

0

Use the current region property:

Set rng = Range("A1").CurrentRegion

This is the equivelant of selection A1 and pressing Ctrl + A

Your error is being caused because you're assigning a row number and a column number to the Range method, where you should have an address or a start/end cells:

'// lets assume row = 5
row = Range("A" & Rows.Count).End(xlUp).row

'// lets assume col = 10
col = Cells(1, Cells.Columns.Count).End(xlToLeft).Column

'// this will produce Range(5, 10) <~~ invalid syntax
Range(row, col).Select

'// consider instead:
Set rng = Range(Cells(1, 1), Cells(row, col))
'// or use the .CurrentRegion property as mentioned above if there are no gaps in your data.

answered May 23 '16 at 11:46

SierraOscar

17,507
6
40
68

This worked, but for some reason, it does not iterate through the sheets. It only exports the one I am currently on. I have editted the above code to reflect the current region property. Any ideas? – MN Beitelmal May 23 '16 at 11:57
1

you need to qualify the range with a sheet object otherwise it will default to the `AcitveSheet`. Instead of `Set rng = Range("A1").CurrentRegion` use `Set rng = Sheets(x).Range("A1").CurrentRegion` – SierraOscar May 23 '16 at 11:59
I have done that, but the only output I get is that for the first sheet. I basically have a table in sheet 1, and a different one in sheet 2. I would like to export each in a separate txt file, but currently I am only able to get one (the one I am currently on). – MN Beitelmal May 23 '16 at 12:04
Sorry @Macro Man, I updated it on my macros but not on this post. I just updated it. Still same problem. Any ideas? – MN Beitelmal May 23 '16 at 12:19
2

All your data is going into the same file - change `myFile = "C:\Users\mohamednuri.beitelma\Desktop\" & ActiveSheet.Name & ".txt"` to `myFile = "C:\Users\mohamednuri.beitelma\Desktop\" & Sheets(x).Name & ".txt"` – SierraOscar May 23 '16 at 12:23

How to select range of cells, from first non-black to last non-blank cell (VBA)?

1 Answers1