0

I have excel worksheets with a usedrange that consists of two dynamic tables/blocks.

On the right-hand side, there is table 1 with raw data (beginning in cell A1). Then, there is an empty column E:E (for example), followed by some formulas in a table beginning at F1 (for example).

I need to only clear the content of table 1. Table 2 needs to remain where it is and I was not yet able to figure out how.

When I do

   Set StartCell1 = Range("A1") ' select first cell of table 1
   StartCell1.CurrentRegion.Select ' attempt to select whole table 1
   Selection.ClearContents ' attempt to clear table 1

Both table 1 and table 2 are deleted.

How to select only the subset of a usedrange that is continuous (i.e. no empty columns/rows) in excel-VBA?

Note that the dimensions of both tables as well as the number of blank columns in between varies between sheets.

sudonym
  • 3,788
  • 4
  • 36
  • 61
  • You need to avoid using the `.Select`. Instead you should look for LastRow, and LastColumn. There are plenty of posts on here that give you a way to calculate and implement those. – Luuklag Dec 08 '17 at 08:06
  • 1
    https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba?answertab=active#tab-top for lastrow – Luuklag Dec 08 '17 at 08:07
  • What happens when you manually select A1 and press Ctrl + Shift + * – Mark Fitzgerald Dec 08 '17 at 08:32
  • needs to be a VBA Sub – sudonym Dec 08 '17 at 08:32
  • I asked because there may be a mis-match between what you think Excel should be seeing as the `CurrentRegion` and what Excel is actually interpreting? The shortcut is equivalent to VBA `Range("A1").CurrentRegion.Select` – Mark Fitzgerald Dec 08 '17 at 08:38
  • Seems something wrong with your worksheet. Otherwise codes seems okay... – Harun24hr Dec 08 '17 at 08:49

0 Answers0