Select cells on both sides of active cell, then select all rows down table with data

I'm a VBA beginner.

I need to copy information in a current Excel file to another file and save as csv. I have 95% of the code working as I want, but one part I can't solve. I've spent the better part of two days experimenting with possible solutions to no avail.

The original Excel files have been created more like a 'Word Document' than an Excel spreadsheet which has created a several challenges:
1. The 'starting row' for the 'table' I need to select and copy is not the same file to file, e.g. the first row of data is on Row 8, or Row 11, or 9, etc.
2. Column 1 and Column 2 of the table can be blank (as this information is optional), so I am using Column 3 which is 'required info' to find the actual 'starting row' for the table and set an "ActiveCell"
3. The number of rows in the table varies from form to form (one could have 49, another 80, another 200, etc.)

This code gets me the three 'pieces' I need:

'Selects the correct cell to find the 'starting row' for the table on every file
Worksheets(1).Select
Worksheets(1).Range("C4").Select
Cells.Find(What:="QTR", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Range("A1").Select
If IsEmpty(ActiveCell.Value) = True Then
ActiveCell.Offset(, 1).Range("A1").Select
End If

' Finds the last row of data in the table after finding the proper 'ActiveCell'
last_QTR_Row = Cells(Rows.Count, 3).End(xlUp).Row

' Selects the required range for the table around the ActiveCell
Range(Cells(Selection.Row, 1), Cells(Selection.Row, 10)).Select

What I can't find the answer for (and have tried for hours with trial and error) is how can I get the 2nd and 3rd elements to work together (or totally take a different approach that works better) to: 1) Select the needed range around the ActiveCell (Column 1: Column 10) and, 2) Select down to the 'last row' of the table as determined by the final cell with data in Column 3.

Any help would be greatly appreciated. I have included a sample file in this post. Thank you so much in advance for your help.

You lost me as a good VBA procedure most of the time must not select any cell !

It's easier to just elaborate what should be done according to rows, columns or cells addresses
and to attach the expected text file …

According to your attachment a VBA demonstration as a beginner starter to export rows to a text file w/o another worksheet
to paste to the Sheet1 (Page 1) worksheet module :

PHP Code: 

Sub Demo1()
        F% = FreeFile
        Open ThisWorkbook.Path & "\Export .csv" For Output As #F
    With UsedRange.Rows
        For R& = Application.Match("FIELD NAMES", .Columns(1), 0) + 2 To Cells(Rows.Count, 3).End(xlUp).Row
            Print #F, Join(Application.Index(.Item(R).Value, 1, 0), ",")
        Next
    End With
        Close #F
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Thank you, Marc L. I so appreciate your help.

I knew there was going to be a much easier way to do what I was trying to do.
Unfortunately, when I step through your procedure I get an "Object Required" error at the 'With.UsedRange.Rows' line.
I've tried a couple times to resolve the error with no luck.

I apologize for not including more info in the spreadsheet from the first post. It was my first time posting here.
I've included the spreadsheet with what the 'finished export data' (ExportData tab) will need to look like in the new file.

I've hacked together some code to do everything except find the 'Field Name table' information on each file and get it pasted, starting at 'F2' on the new sheet.
I'm unsure how to do this because, as I mentioned before, the information can be different lengths on each file and start on different rows.
Also the 'Field Name' and 'RM' columns can be empty or have incomplete data. The 'QTR' column data is required for each form.

Once it is there though, the rest of the VBA works to pull in the other required data.
In the end, my VBA will loop through a folder with 200+ files and read the data on Sheet1 of each file, save 'exported data' to a new file, close, and move to the next file.

I hope the new spreadsheet will help.

Thank you again for your help it will clearly move me lightyears ahead in understanding best practices for this.

Where did you paste the code ? Not as per the direction of my previous post …

Wrong location, same player shoot again ! See post #3 …

If you want a standard module rather than the explicit place explained in post #3
you must add a valid worksheet reference - example : ActiveSheet - to the UsedRange statement like explained in VBA help …

Thank you Marc L.

I learned how to paste the macro into the proper place. You can't step through it to 'test it', but it runs beautifully as expected. Amazing!

Your more expert code is a bit beyond my complete comprehension, but I think I get the general flow.

I'm going to work with your 2nd code piece to take my next step for the 'full transformation' of the data I shared with the 2nd spreadsheet.

Thank you for your continued help.

Thanks for the rep' !

According to your last attachment the direct CSV export rather than using another worksheet
to paste to a standard module but only working with the active sheet :

PHP Code: 

Sub Demo2()
  Const D = ","
        V = Application.Match("FIELD NAMES", ActiveSheet.UsedRange.Columns(1), 0)
    If IsNumeric(V) Then
        S$ = vbNewLine & [B3].Text & D & [B4].Text & D & [I3].Text & D & [I4].Text & D
        F% = FreeFile
        Open ThisWorkbook.Path & "\Export .csv" For Output As #F
        Print #F, "Contact,Farm,Phone,Date,FIELD NAMES,RM,QTR,SEC,TWP,RNG,MER,Acres,2018 CROP,Seeded Date";
    With Cells(V, 1).CurrentRegion.Rows
        For R& = 3 To .Count - 1:  Print #F, S; Join(Application.Index(.Item(R).Value, 1, 0), D);:  Next
    End With
        Close #F
    End If
End Sub 


Wow, Marc L.!! I finally had the chance to work this bit of code into the larger piece I have been working on and it works incredibly! Thank you, so much.

You're 'Demo2' sub procedure looks amazing too. It feels a bit beyond my ability to tailor it to the specific needs for this project.

Example: as the code loops through each file in the folder and saves the required information, I need each new file to save with the cell value of B2 (farm name) and the 'year' part of the value contained in M2 of the new table, separated with a dash: Green Acres Farms - 2018.csv, for example (for all 250 files).

I'm going to try to find a way to append the code that currently does that file naming for me to your Demo2, but if I 'blow up' what you've made, I will go back to use my very ugly, piecemeal approach for the remainder of this project. I am still WAY ahead because of your help with the 'mass selection method' for the tables that vary from file to file on Sheet 1! So amazing.

Your code is 100x times more elegant than my piecemeal approach. You have given me a whole new understanding of the power of VBA and what someone who is well-versed in it is capable of. Is there a specific resource you started with to learn the foundations of VBA? Thank you, again.

In fact VBA is a mix of Visual Basic and an Excel objects model with Excel features as well …

A good way to start learning is to activate the Macro Recorder and operate manually in order to see which statements are used.
But as a generated code must be rewritten to be clean, comparing with forums - or just asking on - can lead to some good practice …