Select all Except Top Row and Left Column

mktmkt

New Member
Joined
Jan 19, 2014
Messages
16
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.

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

Thanks
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,686
Office Version
  1. 2010
Platform
  1. Windows
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.

Code:
Range("B2:B,CC2:CC").Select
Try it like this...

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

mktmkt

New Member
Joined
Jan 19, 2014
Messages
16
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,686
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top