PDA

View Full Version : Solved: Remove A Fraction Of A Range From A Range Variable



Mavyak
09-02-2008, 07:13 PM
Is it possible to remove a protion of a range from a range variable. Let's say I have range A1:K45 selected and I want to make it all upper-case except for cells B42 and J12.

Sub MakeCaps()
Dim c As Range, c1 As Range

Set c = Selection

'Is something like the following two lines possible?
'Set c = c.Remove("B42")
'Set c = c.Remove("J12")

For Each c1 In c
c.Value = UCase(c.Value)
Next c1

Set c1 = Nothing
Set c = Nothing
End Sub
This is all academia and is not currently posing a problem for me (other than my mild o.c.d.) so please dedicate time to others' problems first.

Thanks.

Mav

mdmackillop
09-03-2008, 12:39 AM
How about

Sub MakeCaps()
Dim c As Range, c1 As Range

Set c = Selection

'Is something like the following two lines possible?
'Set c = c.Remove("B42")
'Set c = c.Remove("J12")

For Each c1 In c
Select Case cl.Address(0, 0)
Case "B42", "J12"
'do nothing
Case Else
c.Value = UCase(c.Value)
End Select
Next c1

Set c1 = Nothing
Set c = Nothing
End Sub

Bob Phillips
09-03-2008, 01:38 AM
Sub MakeCaps()
Dim c As Range, c1 As Range

Set c = Selection

For Each c1 In AntiUnion(c, Union(Range("B12"), Range("J12")))
c1.Value = UCase(c1.Value)
Next c1

Set c1 = Nothing
Set c = Nothing
End Sub


'-----------------------------------------------------------------
Function AntiUnion(SetRange As Range, UsedRange As Range) As Range
'-----------------------------------------------------------------
Dim saveSet
saveSet = SetRange.Formula
SetRange.ClearContents
UsedRange = 0
Set AntiUnion = SetRange.SpecialCells(xlCellTypeBlanks)
SetRange = saveSet
End Function

TomSchreiner
09-03-2008, 05:31 AM
AntiUnion? :)

mdmackillop
09-03-2008, 05:34 AM
He's a Scottish Nationalist at heart.:devil2:

Bob Phillips
09-03-2008, 05:45 AM
It's a naff name I know, but I couldn't think of anything else (except maybe ExUnion),

Mavyak
09-03-2008, 06:48 AM
Well done, xld! I was hoping for a built in method/property for a Range object, but in its absence, your code seems the cleanest solution.

Bob Phillips
09-03-2008, 06:52 AM
I hate to disagree after you thanked me. Whilst mine is probably more extensible, in this case MDs is the cleanest.

Bob Phillips
09-03-2008, 06:53 AM
He's a Scottish Nationalist at heart.:devil2:

No, I believe in LOCAL income tax, not a national income tax.

TomSchreiner
09-03-2008, 07:17 AM
MD. Seeing that you are an MS MVP, I think that you should recommend an improvement to version 13. AntiUnion as a method of the XLD class. :)

Mavyak
09-03-2008, 07:42 AM
I hate to disagree after you thanked me. Whislt mine is probably more extensible, in this case MDs is the cleanest.
I should clarify:

MD's is cleaner, but it does not actually remove a portion of a range from the range variable. So if I wanted to pass the range (less the undesired portions of the range) to another Sub/Function, I'd have to use yours. If I used MD's, I'd have to write the Select Case structure into any Sub/Function I passed the range variable to. Since your code actually removes the ranges from the range variable, it would make the project's code cleaner overall.

:thumb


Edit: My philosophy professor marked me down five points on a paper for using the word "whilst." I should send him a link to this forum.

Bob Phillips
09-03-2008, 08:11 AM
MD's is cleaner, but it does not actually remove a portion of a range from the range variable. So if I wanted to pass the range (less the undesired portions of the range) to another Sub/Function, I'd have to use yours. If I used MD's, I'd have to write the Select Case structure into any Sub/Function I passed the range variable to. Since your code actually removes the ranges from the range variable, it would make the project's code cleaner overall.

Which was my point, MDs was crisp and concise, but specific, mine is extensible, as you are proving :-)


Edit: My philosophy professor marked me down five points on a paper for using the word "whilst." I should send him a link to this forum.

Whilst is a lovely word, and perfectly correct usage here I believe. What was his argument against?

Mavyak
09-03-2008, 10:17 AM
Whilst is a lovely word, and perfectly correct usage here I believe. What was his argument against?

I think he was giving me a hard time. I believe he only docked me 1 point (which wasn't enough to change the letter grade) and he added the comment, "Are you British?". I can't even remember the context I used it in. That was over a decade ago.

Bob Phillips
09-03-2008, 10:26 AM
So he is saying that only the British (probably meant English) use (should use?) the word Whilst LOL!

Mavyak
09-03-2008, 10:33 AM
I guess. I didin't argue with professors of subjective topics (philosophy, English, speech, etc.). My grades depended too much on their opinion of the subject (and me).