Selecting several tables of varying size in non-contiguous range

[Kentetsu]

I have a sheet with 6 tables of varying size (length & width). The furthest Column used will be Column J.

So, I need to start at the bottom of Column A and go up to find the first populated cell, then extend that range to the top of the sheet and over to Column J.

I don't know why I'm having so much difficulty with this, as I've done it before and it was pretty simple from what I recall. But I've been wracking my brain on this all day and getting no traction at all.

If anyone can give me a hand with this, I would really appreciate it.

 

[Domenic]

Assuming that Column A contains text values, try defining the range as follows...

=$A$1:INDEX($J:$J,MATCH(REPT("z",255),$A:$A,1))

If Column A contains numerical values, replace...

REPT("z",255)

with

9.99999999999999E+307

Hope this helps!

 

[Kentetsu]

Thanks for taking the time to think on this Domenic. Unfortunately, Column A contains text, numbers, and dates.

I managed to declare a cell at the bottom corner of the range, intending to perform a selection between A1 and "EndOfData" but couldn't get the selection routine figured out. One of the few times Google has failed me. lol.

I finally broke down this afternoon and used the Macro Recorder, using ctrl up/down/sideways, etc. until the entire range was captured. I'm not happy with it because it isn't very elegant, but since the overall structure of the data shouldn't change I suppose it's a suitable solution.

Just in case you're curious at all, this is a project that involves opening several spreadsheets/copying data/formatting/adding formulas/deleting unneeded stuff/turning everything into tables/and then spitting the results out into an email. It is a one button shift Recap creator. It should save about 20 minutes per shift each day, which amounts to an hour per day, or 365 hours per year at an average rate of $17.00/hour which is $6,205 per year. I love this stuff. I do it in my spare time just for the fun of it.

I've got everything working now, just fine tuning and checking stability along with a few last tweaks.

Thanks again. And if you have any other ideas to tidy this up I'm always a very willing student.

 

[Domenic]

In that case, if Column A does not contain empty cells, try...

=OFFSET($A$1,0,0,COUNTA($A:$A),10)

Does this help?

 

[Peter_SSs]

I need to start at the bottom of Column A and go up to find the first populated cell, then extend that range to the top of the sheet and over to Column J.

Like this?

[color=darkblue]Sub[/color] GetRange()
  [color=darkblue]Dim[/color] rng [color=darkblue]As[/color] Range
  
  [color=darkblue]Set[/color] rng = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 10)
  MsgBox "Is this the range you want?" & vbLf & rng.Address(0, 0)
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Kentetsu]

Domenic,

Column A does contain blank cells. There is an empty row between each Table, otherwise they try to "join together" and get all messed up. So, Table/Blank row/Text (title)/Table/repeat.

Peter_SSs,

I can't test that at the moment but it looks suspiciously correct.

Thank you both for your assistance...

 

[Kentetsu]

Peter_SSs,

The result gives me the last row containing data, extending over to Column J. So, somehow it is not extending the range up to A1.

Here is the hodgepodge I am currently using, which does result in the correct range being selected. Ugly, ain't it?

.Range("PasteFixedRacks")(2, 10).Select                                   (I just realized that this line will not work because the size of the table will be variable)
Selection.Name = "EndRange"                                                  (this is supposed to be the bottom right cell of the desired range)

Application.Goto Reference:="EndRange"
    .Range(Selection, Selection.End(xlUp)).Select
    .Range(Selection, Selection.End(xlUp)).Select
    .Range(Selection, Selection.End(xlUp)).Select
    .Range(Selection, Selection.End(xlToLeft)).Select
    .Range(Selection, Selection.End(xlToLeft)).Select
    Selection.Copy                                                                     (The next step is paste into email)

I was hoping that by setting that single cell as "EndRange" I would then be able to do something like this:

.Range("A1:EndRange").copy

But having a hard time getting anywhere with that as well.

Thanks again...

 

[Domenic]

Peter's suggestion should return the desired result. So, for example, if A10 is the last cell in Column A that contains data, the reference returned should be "A1:J10", which I understand is what you want, correct? What's the reference that it returns?

 

[Kentetsu]

It is only returning the last row that contains data, nothing else.

 

[Domenic]

Let's say that A10 is the last cell in Column A of the active sheet that contains a value, are you saying that the reference returned would be A10:J10?