9

I have some data in an Excel Worksheet. I would like to select all the cells which contain data.

For example, for a worksheet with data in cells A1, A2, A3, B1, B2, B3, C1, C2, and C3, how can I select just this 3x3 grid, and not the entire sheet?

I am looking for something like ActiveSheet.SelectUsedCells.

Brett Donald
  • 6,745
  • 4
  • 23
  • 51
Marius
  • 2,494
  • 6
  • 31
  • 41
  • See also: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba and: https://stackoverflow.com/questions/21557916/excel-macro-select-all-cells-with-data-and-format-as-table – SER Jan 03 '23 at 17:49

2 Answers2

18

Here you go:

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Or if you don't necessarily start at A1:

Range("C6").Select  ' Select a cell that you know you populated'
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
RichieHindle
  • 272,464
  • 47
  • 358
  • 399
17

You might also want to look at the CurrentRegion property. This will select a contiguous range that is bounded by empty cells, so might be a more elegant way of doing this, depending on the format of your worksheet.

For example:

Range("A1").CurrentRegion.Select
cyberponk
  • 1,585
  • 18
  • 19
Lunatik
  • 3,838
  • 6
  • 37
  • 52