Create VBA Range for multiple groups of cell ranges

**motolich** · 08-12-2013, 10:09 AM

I'm not sure if what I'm trying to do is possible, but I would like to be able to create a range in vba for multiple groups of cells that is dynamic (the range will change if I add or remove rows). Currently, I have header rows with dependent rows beneath them (I can add/remove these dependent content rows). In a column to the right in each header row, I have a formula to return the range of cells within these dependent rows (e.g. K20:K31, K33:K40, K42:K99, K101:K105, K107:K116, K118:K131). I also have a cell where I concatenate all these individual ranges (K20:K31,K33:K40,K42:K99,K101:K105,K107:K116,K118:K131).

What I'd like to do is create a code where I can set these values as a range (end-result being that I clear the contents of these cells if criteria are met. I'm using the following code with no success:

Please Login or Register  to view this content.

OTA Checklist!B16 is the cell that has the concatenated ranges.

**motolich** · 08-12-2013, 12:36 PM

Okay, after more searching (Google's my best friend), I found a solution which appears to work:

Please Login or Register  to view this content.

If anyone knows a better way than this, I'd love to know. Thanks.

**patel45** · 08-12-2013, 01:41 PM

As you can see , your code is not available

**AlphaFrog** · 08-12-2013, 01:51 PM

There may be a couple of alternatives depending on the nature of your data.

The .SpecialCells method can select all cells with formulas in column K.
Range("K:K").SpecialCells(xlCellTypeFormulas).Select
This could also select cells with constants (text and\or numbers or blanks)

Or you could create a Named Range and then reference the named range in your code e.g.;
For Each Cell In Range("MyRange")
The named range will change as you add\delete rows. Do a web search for something like Excel Named Range tutorial.

The best way to do it very much depends on the nature of your data.

Create VBA Range for multiple groups of cell ranges

LinkBack

Thread Tools

Rate This Thread

Display

Create VBA Range for multiple groups of cell ranges

Re: Create VBA Range for multiple groups of cell ranges

Re: Create VBA Range for multiple groups of cell ranges

Re: Create VBA Range for multiple groups of cell ranges

Thread Information

Users Browsing this Thread

Similar Threads

If a cell contains two specific groups of text in a range of cells, how to create a link?

[SOLVED] Create macro that searches multiple named ranges and returns name of the range with data

[SOLVED] Create multiple ranges based on an index of values that are also used as range names

Loop Through Range and Create Strings Based on Groups

Copy range of cell data to range of cells arranged by groups

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions