Dynamic Range Help

[c7015]

I'm trying to make a dynamic range based on checked boxes that spans columns c e f g h

[VBA]ElseIf optionbutton1.value = True And optionbutton3.value = True And optionbutton4.value = True And Optionbutton5.value = True And optionbutton6.value = True Then
Dim DynamicRange As Range

ActiveSheet.Select
Range("C20,E20,F20,G20,H20").Select
Range("H20").Activate
Set DynamicRange = Range(Selection, Selection.End(xlDown))
DynamicRange.Select


ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=DynamicRange[/VBA]

but for some reason it will only select the C column and notthe rest (e f g h )

what am I doing wrong here ?

[p45cal]

maybe you're after doing something like:
[VBA]Set DynamicRange = Range(Range("H20"), Range("H20").End(xlDown))
Set DynamicRange = Intersect(DynamicRange.EntireRow, Range("C:C,E:H"))
DynamicRange.Select
[/VBA]Decide which column you want the depth of the range to depend on (H above) and adjust accordingly.

[c7015]

Thanks P45

I actually got it going like this

[VBA]ElseIf optionbutton2.value = True And optionbutton3.value = True And optionbutton4.value = True And Optionbutton5.value = True And optionbutton6.value = True Then

lRw = WorksheetFunction.Max([d20].End(xlDown).Row, [e20:h20].End(xlDown).Row)
Set dRng = Union(Range("d20", "d" & lRw), Range("E20:H" & lRw))
dRng.Select

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=dRng

With Sheets("summary")
lRw = WorksheetFunction.Max(.Range("d27").End(xlDown).Row, .Range("e27:h27").End(xlDown).Row)
Set dRng = Union(.Range("d27", "d" & lRw), .Range("E27:H" & lRw))
dRng.Select

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=dRng
End With[/VBA]

but it wont seem to take my second chart from the non active page , giving me a select method of range class failed , debugging on the 2nd dRng.select line ??

[p45cal]

but it wont seem to take my second chart from the non active page , giving me a select method of range class failed , debugging on the 2nd dRng.select line ??

Probably because the sheet summary is not the active sheet.
You probably don't need that select line at all - try it commented out.

This is unusually convoluted!:
Set dRng = Union(Range("d20", "d" & lRw), Range("E20:H" & lRw))

It is the same as:
Set dRng = Range("D20:H" & lRw)

Even if you wanted non-contiguous ranges as implied in you first post (you used C instead of D) then
Set dRng = Union(Range("c20", "c" & lRw), Range("E20:H" & lRw))
can be simplified a bit to:
Set dRng = Union(Range("c20:c" & lRw), Range("E20:H" & lRw))

[c7015]

Probably because the sheet summary is not the active sheet.
You probably don't need that select line at all - try it commented out.

This is unusually convoluted!:
Set dRng = Union(Range("d20", "d" & lRw), Range("E20:H" & lRw))

It is the same as:
Set dRng = Range("D20:H" & lRw)

Even if you wanted non-contiguous ranges as implied in you first post (you used C instead of D) then
Set dRng = Union(Range("c20", "c" & lRw), Range("E20:H" & lRw))
can be simplified a bit to:
Set dRng = Union(Range("c20:c" & lRw), Range("E20:H" & lRw))

Thanks P45 Your on the money , I took it out and it works much better ..


perhaps you can help me with another issue I am having with my same macro


Ok so I have a macro that checks for an age in column a , if the age matches the macro will offset to the specific value and clear everything under the age

[VBA]Set findit = Range("A21:A130").Find(what:=rbox1.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 2).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

Set findit = Range("A21:A130").Find(what:=t20ra.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 3).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

Set findit = Range("A21:A130").Find(what:=nt10ra.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 4).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

Set findit = Range("A21:A130").Find(what:=nt20ra.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 5).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If[/VBA]

and that works fine for what I want , however

now I want to switch sheets and do the same thing on a sheet called "summary" with the range A28:A127

so I copy the whole code but change the range and add it after what I have (as well as tell it to change sheets)

[VBA]Sheets("summary").Select
cells(28, 1).Value = Age.Value + 1
Selection.AutoFill Destination:=Range("A28:A127"), Type:=xlFillSeries
Range("A28:A127").Select

Set findit = Range("A28:A127").Find(what:=rbox1.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 2).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

Set findit = Range("A28:A127").Find(what:=t20ra.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 3).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

Set findit = Range("A28:A127").Find(what:=nt10ra.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 4).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

Set findit = Range("A28:A127").Find(what:=nt20ra.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 5).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

End Sub[/VBA]

but it must be too many find it cases because it crashes my macro ...
how can I do this better ?


thanks

[p45cal]

on which line does it crash?

[c7015]

on which line does it crash?

It dosent return a line , the whole thing just crashes

But it seems like its on the second find it command after I change sheets


I was hoping there was a way to loop the first set of code with a new range
On the summary page

[p45cal]

the whole thing just crashes

You have to reboot the computer? Reboot Excel? What? Is there any error message - what does it say?

But it seems like its on the second find it command after I change sheets

What makes you say it seems its on the second find it?

I was hoping there was a way to loop the first set of code with a new range On the summary page

There is but I need to understand what crashes it first.
Can you attach the file here?

[c7015]

this line

[VBA]Set findit = Range("A28:A127").Find(what:=t20ra.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 3).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If [/VBA]

as it clears the first range and then crashes without doing this line


it crashes the macro , not excel or the computer

[p45cal]

So no error message at all.

Where is the code? A standard code module, a sheet code module, or even perhaps the Thisworkbook code module?

One thing I would do is to remove all (or as much as possible) Select operations. So the likes of:

[VBA]findit.Select
ActiveCell.Offset(1, 3).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents [/VBA]

becomes:
[VBA]With findit
Range(.Offset(1, 3), .Offset(1, 3).End(xlDown)).ClearContents
end with[/VBA]

I'll wait for a reply before suggesting any more.

[c7015]

So no error message at all.

Where is the code? A standard code module, a sheet code module, or even perhaps the Thisworkbook code module?

One thing I would do is to remove all (or as much as possible) Select operations. So the likes of:

[VBA]findit.Select
ActiveCell.Offset(1, 3).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents [/VBA]

becomes:
[VBA]With findit
Range(.Offset(1, 3), .Offset(1, 3).End(xlDown)).ClearContents
end with[/VBA]

I'll wait for a reply before suggesting any more.

It's just a standard code module , button clicks to export and trim all unessasary data ( ie anything past whatever age I type in my text box(s)(4 of them)

So say I type 85 in the text box called "rbox1" it will clear all data in colum c after it finds the rbox 1 value

There are 4 text boxes and as many as 3 at a time might have to be trimmed at different values

I will attach the sheet when I get back to my computer

[c7015]

Ok P45 , here is the sheet

[c7015]

here are some typical values

[/URL]




Uploaded with ImageShack.us
and the button that I am working on

[c7015]

[/URL]

as you can see it works to clear what I want on the page "calculations"


but fails on the page Summary (however it does the first opperation clearing column c at the correct age ??

[p45cal]

The code is not crashing at all. It is coming to and End statement, because t20ra was empty so nothing was found in the range searched.
End is a bit draconian:"Terminates execution immediately. Never required by itself but may be placed anywhere in a procedure to end code execution, close files opened with the Open statement and to clear variables."

What do you want to happen when something like this is encountered.

[c7015]

The code is not crashing at all. It is coming to and End statement, because t20ra was empty so nothing was found in the range searched.
End is a bit draconian:"Terminates execution immediately. Never required by itself but may be placed anywhere in a procedure to end code execution, close files opened with the Open statement and to clear variables."

What do you want to happen when something like this is encountered.

well If there is no value in t20 ra it means it is not being compaired and can just move onto the next statement

how come it does not end the program when it is checkcking the first page calculations ..


I would like it to do exactly what it does on the first page but on the sheet summary , is it just where I have place the end statement

[c7015]

ok so I just took out the end and it works , but not sure why its different from the fist set to the second ...

[p45cal]

well If there is no value in t20 ra it means it is not being compaired and can just move onto the next statement

how come it does not end the program when it is checkcking the first page calculations ..

Because on the first sheet you're searching ranges A21:A130, which includes some empty cells, but on the Summary sheet, your code only searched A28:A127, which doesn't include empty cells.

I would like it to do exactly what it does on the first page but on the sheet summary , is it just where I have place the end statement

No.

You need to change the likes of[vba]If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 5).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If[/vba]to:[vba]If not findit Is Nothing Then Range(findit.Offset(1, 5), findit.Offset(1, 5).End(xlDown)).ClearContents[/vba]Yes, one line.