How to exclude some of the excel range from the output to text file

[Henceman]

The code I have is following:

And the data it is extracting from is this:

As you see, the amount of data there might be, varies, but the extract takes a fixed range into account, to cover everything.
Now there is a problem, it extracts indeed all the data, but also creates a lot of whitespaces, so if the actual data is in example 10 lines final, the txt file will always be 1000 lines, which of 990 will be whitespace, which makes it impossible to process further.

How can I tell excel to extract only seeable data from the whole range, so txt output only containt the rows visible by user, not the hidden formulas?

 

[jasonb75]

Try something like

With ActiveSheet
    Set WorkRng = .Range("A1", .Range("AM:AM").Find("?*", .Range("AM1"), xlValues, xlWhole, xlByRows, xlPrevious))
End With

 

[Henceman]

Can you help me integrating this to the code, I will receive an errori with this, regardless were its located.

 

[Fluff]

It would be easier if you supplied your code, rather than expecting us to type it all out again.

When posting code please use code tags, the # icon in the reply window.

 

[Henceman]

Sorry, I forgot I submitted code in image form

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False 'screen wont flicker when saving when turned to False.
'True will visibly update the Excel worksheet on your screen any time there is a change that happens within the worksheet, which makes the macro run slow.

'This part extract the data from sheet and saves it into .txt file
Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range


Set WorkRng = Application.Selection
[COLOR=#ff0000]Set WorkRng = ActiveSheet.Range(Cells(1, 46)) 'the range of cells which will be saved to txt file.[/COLOR]


Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.Copy
wb.Worksheets(1).Paste
saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = False
Application.ScreenUpdating = True

The code above is a little different than before, since I managed to get a working solution with creating extra count cells to worksheet and reference them as a source for final row to take into account, but I would consider this as a bad band aid, so Getting the correct row in code would be ideal.

 

[Fluff]

Thanks for that, try

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False 'screen wont flicker when saving when turned to False.
'True will visibly update the Excel worksheet on your screen any time there is a change that happens within the worksheet, which makes the macro run slow.

'This part extract the data from sheet and saves it into .txt file
Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range


With ActiveSheet
    Set WorkRng = .Range("A1", .Range("AM:AM").Find("?*", .Range("AM1"), xlValues, xlWhole, xlByRows, xlPrevious))
End With

Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.Copy
wb.Worksheets(1).Paste
saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = False
Application.ScreenUpdating = True

 

[Henceman]

Still no go.
https://imgur.com/a/2Wt42Pq

 

[Fluff]

What error message & number do you get?
Also is there any column that will always have data (not a formula) on the last row?

 

[Henceman]

Error is 1004, Application defined or object-defined error.

No, there are only formulas. About 12 columns (two rows of column headers) and each has formula for 1000 lines down. No certain cell which is definable end.

 

[Fluff]

If you only have 12 columns that's the problem, Jason was going by your code which is up to column AM.
Try

Dim UsdRws As Long

With ActiveSheet
   UsdRws = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
    Set WorkRng = .Range("A1:Z" & UsdRws)
End With