Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select uneven range
Try this statement...
Union(Range("B2:B3"), Range("C2:" & Range("C2").End(xlDown))).Select Usually you don't have to select the cells in order to do things to them. Perhaps this previous posting of mine (a response to another person using Select/Selection type constructions) will be of some help to you in your future programming... Whenever you see code constructed like this... Range("A1").Select Selection.<whatever you can almost always do this instead... Range("A1").<whatever In your particular case, you have this... Range("C2:C8193").Select 'select cells to export For Each r In Selection.Rows which, using the above concept, can be reduced to this... For Each r In Range("C2:C8193").Rows Notice, all I have done is replace Selection with the range you Select(ed) in the previous statement and eliminate the process of doing any Select(ion)s. Stated another way, the Selection produced from Range(...).Select is a range and, of course, Range(...) is a range... and, in fact, they are the same range, so it doesn't matter which one you use. The added benefit of not selecting ranges first is your active cell does not change. -- Rick (MVP - Excel) "Jake" wrote in message ... I would like to select a range from cells B2:B3 and from C2 to the last contiguous cell below C2. I've tried currentregion and a few other pieces of code with now success. Thanks, Jake |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select uneven range
thanks Rick,
I've made an error in my question. I want to set a range variable and I realize I need the range B2:C2 to last contiguous cell in column C, not just B2:B3. I've tried this code: Set rngDSUMCriteria = Range("B2"), Range("C2:" & Range("C2").End(xlDown)) but it has an error and I'm not sure why "Rick Rothstein" wrote: Try this statement... Union(Range("B2:B3"), Range("C2:" & Range("C2").End(xlDown))).Select Usually you don't have to select the cells in order to do things to them. Perhaps this previous posting of mine (a response to another person using Select/Selection type constructions) will be of some help to you in your future programming... Whenever you see code constructed like this... Range("A1").Select Selection.<whatever you can almost always do this instead... Range("A1").<whatever In your particular case, you have this... Range("C2:C8193").Select 'select cells to export For Each r In Selection.Rows which, using the above concept, can be reduced to this... For Each r In Range("C2:C8193").Rows Notice, all I have done is replace Selection with the range you Select(ed) in the previous statement and eliminate the process of doing any Select(ion)s. Stated another way, the Selection produced from Range(...).Select is a range and, of course, Range(...) is a range... and, in fact, they are the same range, so it doesn't matter which one you use. The added benefit of not selecting ranges first is your active cell does not change. -- Rick (MVP - Excel) "Jake" wrote in message ... I would like to select a range from cells B2:B3 and from C2 to the last contiguous cell below C2. I've tried currentregion and a few other pieces of code with now success. Thanks, Jake |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select uneven range
The most obvious thing wrong with your code is you didn't use the Union
function with it. The Union function "joins" non-contiguous ranges together, you just list each range that you want "joined" in a comma separated list. There appears to be some problem with how you attempted to form those ranges in the code you just posted; however, for what you now want, which appears to be a contiguous range (which is markedly different from your original question which dealt with "uneven", what I call non-contiguous, ranges), you won't need the Union function at all. Set rngDSUMCriteria = Range("B2:C" & Range("C2").End(xlDown).Row) -- Rick (MVP - Excel) "Jake" wrote in message ... thanks Rick, I've made an error in my question. I want to set a range variable and I realize I need the range B2:C2 to last contiguous cell in column C, not just B2:B3. I've tried this code: Set rngDSUMCriteria = Range("B2"), Range("C2:" & Range("C2").End(xlDown)) but it has an error and I'm not sure why "Rick Rothstein" wrote: Try this statement... Union(Range("B2:B3"), Range("C2:" & Range("C2").End(xlDown))).Select Usually you don't have to select the cells in order to do things to them. Perhaps this previous posting of mine (a response to another person using Select/Selection type constructions) will be of some help to you in your future programming... Whenever you see code constructed like this... Range("A1").Select Selection.<whatever you can almost always do this instead... Range("A1").<whatever In your particular case, you have this... Range("C2:C8193").Select 'select cells to export For Each r In Selection.Rows which, using the above concept, can be reduced to this... For Each r In Range("C2:C8193").Rows Notice, all I have done is replace Selection with the range you Select(ed) in the previous statement and eliminate the process of doing any Select(ion)s. Stated another way, the Selection produced from Range(...).Select is a range and, of course, Range(...) is a range... and, in fact, they are the same range, so it doesn't matter which one you use. The added benefit of not selecting ranges first is your active cell does not change. -- Rick (MVP - Excel) "Jake" wrote in message ... I would like to select a range from cells B2:B3 and from C2 to the last contiguous cell below C2. I've tried currentregion and a few other pieces of code with now success. Thanks, Jake |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Uneven column lookup | Excel Worksheet Functions | |||
Compare 2 uneven colums | Excel Discussion (Misc queries) | |||
uneven ranges and coding | Excel Discussion (Misc queries) | |||
Font looks uneven in spreadsheet | Excel Discussion (Misc queries) | |||
lookup with uneven columns | Excel Worksheet Functions |