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?

 

[Henceman]

Oh, Im doing a bad job at my own data

Its more complicated than it looks, currently there are about 12 columns for data, but indeed, actual potential range of data is from A1:AK1000


For only 12 columns, the formulas are mapped, the rest is simply empty.

So in my opionion, the formula could calculate the end row from the column A, since column A is always filled, the rest of the columns depend on the data and are (might be) mostly empty anyways.


So formula takes the starting cell always from A1, counts down A column ( actually 4 first columns are always filled) until no more actual values are present, and then sets it as last row with the AK column.

 

[Fluff]

In that case try

Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range

[COLOR=#0000ff]Set WorkRng = Range("A1:AK" & Range("A" & Rows.Count).End(xlUp).Row)[/COLOR]

Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.Copy

 

[jasonb75]

That is going to pick up the formula blanks as well though, Fluff.

I thought that the intention was to avoid cells with a formula nullstring, which was why I went with the wildcard find method. Did I miss something?

 

[Henceman]

Jason, yes, Indeed I dont want cells with formulas to be in the output file- BUT, if the final file should be let say, 100 rows, then if there are whitespaces left on other columns of formulas, this is not an issue- until the file does not exceed 100 rows in total.

Also I did not explain in the tread start, that there is no formulas in all of the cells.

I also include a picture with formulas showing
https://imgur.com/a/Bf5RaOQ (click on image for zoom)

So, the total range is still from A1 to AK1000, BUT it should only capture these rows into txt file, which have first or second column filled.
Like, if there are 100 rows of data, only filling 4 first columns (rest of the columns might have or might not have formulas in place), then the range selected should be A1:AK100.

 

[jasonb75]

Ok, using the code from post #6 , just this line needs changing

Set WorkRng = .Range("AK1", .Range("A:A").Find("?*", .Range("A1"), xlValues, xlWhole, xlByRows, xlPrevious))

as long as there are no other problems with the code that have not yet been discovered, then this should do as needed.

 

[Fluff]

I thought that the intention was to avoid cells with a formula nullstring, which was why I went with the wildcard find method. Did I miss something?

No, I think I misinterpreted this

So in my opionion, the formula could calculate the end row from the column A, since column A is always filled

to mean that col A had values rather than formulae.

 

[Henceman]

Thank you both, the final code helped me through this, I just need to take time to understand the syntax, so I know how to use it the next time I need something like this!