Exclude Header from Range?

N

nastech

Hi, can I exclude a header from a range in an equation, i.e.: "C:D" to
exclude 5 rows from the top? Thanks
 
R

Roger Govier

Hi

Depends what you are trying to do.
You could have =SUM(C:D)-SUM(C1:D5) for example.
 
N

nastech

using: (must be doing something wrong)
=SUM(C:D)-SUM(C1:D5) or =SUM(C6:D65000)

Ran into a problem tried both, may have worked, but received: runtime
error, method of range failed
If Not Intersect(Me.Range("SUM(AK:AK)-SUM(AK1:AK35)"), .Cells) Is
Nothing Then
If Not Intersect(Me.Range("SUM(AK35:AK6500)"), .Cells) Is Nothing Then

the following works, without using above
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Me.Range("AK:AK"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(.Row, "AH")
.NumberFormat = "dd"
.Value = Now
End With
Application.EnableEvents = True
End If
If Not Intersect(Me.Range("AN:AQ"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(.Row, "AV")
.NumberFormat = "dd"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub

XXXXXXXXXX
 
N

nastech

using: (must be doing something wrong)
=SUM(C:D)-SUM(C1:D5) or =SUM(C6:D65000)

Ran into a problem tried both, may have worked, but received: runtime
error, method of range failed
If Not Intersect(Me.Range("SUM(AK:AK)-SUM(AK1:AK35)"), .Cells) Is
Nothing Then
If Not Intersect(Me.Range("SUM(AK35:AK6500)"), .Cells) Is Nothing Then

the following works, without using above
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Me.Range("AK:AK"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(.Row, "AH")
.NumberFormat = "dd"
.Value = Now
End With
Application.EnableEvents = True
End If
If Not Intersect(Me.Range("AN:AQ"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Cells(.Row, "AV")
.NumberFormat = "dd"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub

XXXXXXXXXX
 
Top