Selecting Contiguous data that contains many empty cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello !

I am working on a big spreadsheet that contains dozens of smaller tables. All of these smaller tables have the word "keyword" in the cell at their top-left corner. I have used the code

Worksheets(1).UsedRange.Find("keyword"

and also the method "FindNext" to successfully locate all cells that contain the "keyword" sucessively in a loop. My problem is, I need to SELECT each of those small tables to their entirety, even if there are blank cells on the first row, or in the first column, or anywhere. Basically, I need to select all rows and columns pertaining to the range of contiguous data, even though the table may look like a "swiss cheese" of information, as long as there are no empty rows or columns crossing somewhere in the middle of the table

Do you think you could help me

Thank you in advance!
 
range("Z10").CurrentRegion

as long as there is no comletely blank row or column within the data.

in the example, if AA10, AA11 and Z11 were all blank, it would fail because
the "Data" would just be Z10 at that point. Other than that, it should work
within the limitations stated.

--
Regards,
Tom Ogilvy

Rodrigo said:
Hello !!

I am working on a big spreadsheet that contains dozens of smaller tables.
Click to expand...
All of these smaller tables have the word "keyword" in the cell at their
top-left corner. I have used the code
Worksheets(1).UsedRange.Find("keyword")

and also the method "FindNext" to successfully locate all cells that
Click to expand...
contain the "keyword" sucessively in a loop. My problem is, I need to
SELECT each of those small tables to their entirety, even if there are blank
cells on the first row, or in the first column, or anywhere. Basically, I
need to select all rows and columns pertaining to the range of contiguous
data, even though the table may look like a "swiss cheese" of information,
as long as there are no empty rows or columns crossing somewhere in the
middle of the table.
 
Hi, and thanks for your help

However, I tried to do what you recommended (using the CurrentRegion property) but that is not working. It is selecting the entire area within the boundaries of the data on the worksheet. It is performing as if I was using the "UsedRange" property... what to do, what to do...

Thanks again

Rodrigo
 
Problem solved!!

I found that there were spaces in adjacent cells that were supposed to be empty! Thank you! Your solution works

Rodrigo
 
Back
Top