Select all Except Top Row and Left Column

[mktmkt]

Hi just a quick question

Is it possible to select all the cells in worksheet except for the most left column and the top row?

If not I would settle for Columns B through to CC, minus the top row. I tried to do this in the below code. Unsurprisingly it didn't work.

Range("B2:B,CC2:CC").Select

Thanks

 

[Rick Rothstein]

Hi just a quick question

Is it possible to select all the cells in worksheet except for the most left column and the top row?

If not I would settle for Columns B through to CC, minus the top row. I tried to do this in the below code. Unsurprisingly it didn't work.

Range("B2:B,CC2:CC").Select

Try it like this...

Range("B2", Cells(Rows.Count, Columns.Count)).Select

 

[mktmkt]

Perfect - Thanks Rick Rothstein!
Would you mind briefly explaining how the ".count" coman works. I would never have thought about using it like this.

 

[Rick Rothstein]

Perfect - Thanks Rick Rothstein!
Would you mind briefly explaining how the ".count" coman works. I would never have thought about using it like this.

Rows is a property of a range object... if you omit a reference for Rows, it defaults to Cells (which is a reference to all the cells on the worksheet). So, when I said Rows.Count, I could have also said Cells.Rows.Count and both would return the same value (the same is true for Columns as it is for Rows). Count is a property of a range object which tells you how many of that type of range is in it. So both Rows.Count and Cells.Rows.Count tell you how many rows are on the worksheet. Similarly, both Columns.Count and Cells.Columns.Count tell you how many columns are on the worksheet. Cells(Row.Count,Cells.Count) is a single cell reference to the bottom-rightmost cell on the worksheet. When you give the Range object two arguments where those arguments are either addresses or cell references (in any combination) separated by a comma (note, this is not the same as putting two addresses separated by a comma inside quote marks), it specifies a range with the first argument defining the upper left corner of a range and the second argument defining the bottom right corner of that range. If you follow each step I outlined above, you will see that is how the range that the code line I posted for you is created in order to be selected.

 

[mktmkt]

My understanding is a lot clearer now - thank you!