Run macro automatically when a cell in a dynamic range changes
 

Hi

my problem is hopefully pretty simple.

my code so far looks like this:

Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
If Union(target, Range("$B$76:$B$114")).Address = "$B$76:$B$114" Then
Run ("Model.Hide")
End If
Application.ScreenUpdating = True
End Sub

which works fine for the range $B$76:$B$114 however the row numbers are
prone to change.

I have setup a table thats location will not change, which will store
the reference start and the reference ends. This is located at B8
(start) and C8 (End).

what i am tring to do is get the information from B8 and C8 to replace
the B76 and B114 respectively.

any ideas?

Run macro automatically when a cell in a dynamic range changes
 

Hi Glimmer23.

Try:

Private Sub Worksheet_Change(ByVal target As Range)

Dim startCell As Range
Dim endCell As Range
Dim rng As Range

Set startCell = Me.Range(Me.Range("B8").Value)
Set endCell = Me.Range(Me.Range("C8").Value)

Set rng = Range(startCell, endCell)

If Not Intersect(target, rng) Is Nothing Then
Application.ScreenUpdating = False
Run "Model.MyHide"
End If
Application.ScreenUpdating = True

End Sub

I have intentionally changed the name of the called macro from Hide to
MyHide, because 'Hide' as a special significance for VBA and and the
potential subsequent confusion may cause subtle problems.


---
Regards,
Norman



wrote in message
oups.com...
Hi

my problem is hopefully pretty simple.

my code so far looks like this:

Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
If Union(target, Range("$B$76:$B$114")).Address = "$B$76:$B$114" Then


End If
Application.ScreenUpdating = True
End Sub

which works fine for the range $B$76:$B$114 however the row numbers are
prone to change.

I have setup a table thats location will not change, which will store
the reference start and the reference ends. This is located at B8
(start) and C8 (End).

what i am tring to do is get the information from B8 and C8 to replace
the B76 and B114 respectively.

any ideas?

Run macro automatically when a cell in a dynamic range changes
 

sRange = b78 & ":" & C78
If Not Intersect(target, Range(sRange)) Is Nothing Then

--
HTH

Bob Phillips

wrote in message
oups.com...
Hi

my problem is hopefully pretty simple.

my code so far looks like this:

Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
If Union(target, Range("$B$76:$B$114")).Address = "$B$76:$B$114" Then
Run ("Model.Hide")
End If
Application.ScreenUpdating = True
End Sub

which works fine for the range $B$76:$B$114 however the row numbers are
prone to change.

I have setup a table thats location will not change, which will store
the reference start and the reference ends. This is located at B8
(start) and C8 (End).

what i am tring to do is get the information from B8 and C8 to replace
the B76 and B114 respectively.

any ideas?

Run macro automatically when a cell in a dynamic range changes
 

YOUR THE KING - been tring to get that for ages :) works as just hide -
but ill see if by change both to my hide if it speeds it up

thank you very much for your help

Run macro automatically when a cell in a dynamic range changes
 

Hi Glimmer23,

been tring to get that for ages :) works as just hide -
but ill see if by change both to my hide if it speeds it up

Using 'MyHide' (instead of 'Hide') will not increase execution speed. It is
offered, purely as advice, to prevent VBA from being confused later when,
perhaps, your code becomes more complex and more difficult to debug.

As a general rule of thumb, it is adivisable to avoid the use of VBA
reserved words in any elective naming process.

---
Regards,
Norman



wrote in message
oups.com...
YOUR THE KING - been tring to get that for ages :) works as just hide -
but ill see if by change both to my hide if it speeds it up

thank you very much for your help

Run macro automatically when a cell in a dynamic range changes
 

oops, should be

sRange = range("b78").Value & ":" & range("C78")
If Not Intersect(target, Range(sRange)) Is Nothing Then
--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...
sRange = b78 & ":" & C78
If Not Intersect(target, Range(sRange)) Is Nothing Then

--
HTH

Bob Phillips

wrote in message
oups.com...
Hi

my problem is hopefully pretty simple.

my code so far looks like this:

Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
If Union(target, Range("$B$76:$B$114")).Address = "$B$76:$B$114" Then
Run ("Model.Hide")
End If
Application.ScreenUpdating = True
End Sub

which works fine for the range $B$76:$B$114 however the row numbers are
prone to change.

I have setup a table thats location will not change, which will store
the reference start and the reference ends. This is located at B8
(start) and C8 (End).

what i am tring to do is get the information from B8 and C8 to replace
the B76 and B114 respectively.

any ideas?

Run macro automatically when a cell in a dynamic range changes
 

Ya didnt relise it was a reserved word - but i know that rule well and
changed it after i made my last post to MyHide because i have run into
reserved name problems before...

thanks again for your help.