Select a range using a variable

[MisterMo]

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.

 

[Richard Schollar]

Hi
Perhaps:

Dim lngLastROw As Long

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

Range("C15","W" & lngLastRow).ClearContents

 

[MisterMo]

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.

 

[Richard Schollar]

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!