Select a range using a variable

MisterMo

New Member
Joined
Feb 16, 2011
Messages
8
I am trying to select a range to do a "ClearContents" on and can't seem to "discover" how to select a cell that will always remain the same (C15) and select the second (lower right) corner of the range which will be the last populated cell in column W, always beginning at W15 (the entire range selection will be populated). The second (lower right) corner of the range's row number will be different each time but will always be column W.

Selecting the first "fixed" cell isn't a problem, however I haven't been successful producing code using a (Dim/Set) cell location in the Range description to point to and include the W? cell.

I do have an old writing macros book, but I believe I need to have taken writing macros 101 before using it. I need a basic explanation of the macro code components and what they do and not do. Anyone have a suggestion of a good "Beginning Macro Writing" book. I am getting close to getting a "Macros for Dummies" book but I have found "Dummies" to be too simplistic in their subject matter (but then, that does sound like where I am ...!). I have learned a lot from this forum and can usually "see" how solutions work but not why. Thanks in advance.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi
Perhaps:

Code:
Dim lngLastROw As Long

lngLastRow = Cells(Rows.Count,"W").End(xlUp).Row

Range("C15","W" & lngLastRow).ClearContents
 
Upvote 0
YES!!!! Thanks Richard!

It looks so simple, I see the logic and even understand the various "commands". You've created a monster!

Just for giggles (and I may have the need in the future?). How would you define the cell selection process, if some row in column W below, and/or above, the range desired is populated?

Thanks again Richard and to those others that have taken a look at my question.
 
Upvote 0
Just for giggles (and I may have the need in the future?). How would you define the cell selection process, if some row in column W below, and/or above, the range desired is populated?

.

So you may only want to clear C15:W200 but you actually have a value in say W220 which my preceding code will also clear?

Then you need to determine how you identify the last row of data that you want to clear and adjust the coding to reflect. Eg if the last numeric value in col W then you would need to identify the last row and perhaps loop backwards until you hit a numeric value. Lots of possibilities!
 
Upvote 0

Forum statistics

Threads
1,222,441
Messages
6,166,053
Members
452,010
Latest member
triangle3

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
Back
Top