Defining semi-fixed and variable ranges

[DE_Tx]

Here is the essence of what I am trying to accomplish:
Save all the files from emails to a daily folder (have the code written for this)
Use the 'list' vba code by Chandoo to open the files, get the data, and put it in the summary workbook.

My Problem is how to define size of the array (data area).

Semi-fixed area - for five of the reports, the data always covers Tab13!A8:Lnnn (the number of rows is variable).
That section always end with a "Total" row with the word "Total" in Annn (e.g. A187).
The ending row can be different on each of the reports. That "Total" row is not the only "Total" row on the sheet and is not the last one either.

Currently I use the following to get the data (first two rows can be combined,I know);

Worksheets("Tab13").Select
Range("A8").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

And then go paste it into the summary workbook in the area defined in the list sheet.

How do I define this area without using the Selection.End method? I looked at Cells and CurrentRegion but our report data has header field names in the row directly above the data and data in column A directly above that. So, CurrentRegion grabs all of that.
Or is the Selection.End method the best and we pass that range to the variable?

We also want to be able to paste this data to a master work book of raw data (saved as an xlsb file).

Now for a variable area.
One report we receive has a series of customer satisfaction questions and results.
There are four specific questions that we are looking for and can find them using a Find string.
Once we find the string, the data starts in column A, two cells below and follows the same pattern using the Selection.End method.
Last column will always be M.
We would repeat these steps for each of the questions.


Based on what I have learned on this forum, I think we should declare these ranges at the beginning of the vba code so we can use them throughout the process.
Declare the variable and its type and then the range(or something like that).
If so, can you please share with me how to declare these ranges. I am learning VBA and working to improve my skills daily.
I have searched the vba forum for 'define range' and found several threads but nothing that really spells it out clearly.
If I have missed an obvious thread, please point me in the right direction.
And, obviously I need to restart my Chandoo VBA class. Never got past the first lesson as was overcome by life events.

Thank you for any assistance you may be able to provide.
I can provide a sanitized copy of some data if it would be helpful.

 

[NARAYANK991]

Hi ,

You have mentioned that the rows are variable , but are the columns also variable ?

The End method is used to detect the last row or last column , with data if you start with a cell which has data. If the number of columns is fixed , then you can use this number instead of detecting the last column using the End method.

A generic piece of code would be something like this :

          With Range("A8")
                firstrow = .Row
                lastrow = .End(xlDown).Row
                firstcol = .Column
                lastcol = .End(xlToRight).Column
                .Resize(lastrow - firstrow + 1, lastcol - firstcol + 1).Copy
          End With

This will only work if there are no blank cells / rows / columns anywhere in the data range.

So if the number of columns is fixed , then you can use that number in the Resize method above , instead of detecting it in the lastcol variable.

Narayan

 

[DE_Tx]

Thank you, Narayan. The columns will be fixed in each instance so will use your suggestion.
I will post back once we get this working.