Trouble triggering an event when UsedRange >=A1500?

Hi all, i am having trouble with the code below, i am trying to get the event to run if the UsedRange is >= Row 1500, i have tried just 1500 instead of Range("A1500").............it seems that UsedRange.Row is always showing 1 even thought there are 2000 lines of data, i am only interested in triggering the event if the UsedRange is >= A1500..............Any ideas?, I am only interested in triggering the event if range("A1:A1500") is full the event must not trigger if any other column is filled up to or past this point, i have =VLOOKUP(F2,EventList,2,FALSE) and others in column "I" down to Row 3000 this is the reason i only wanted to trigger the event if the data existed in column A >=1500.

One other problem i have is where i delete cells between A2:H1000 and move cells up the formulae i have in column "I" shows #REF! where it no longer references the cells i set i.e F2 how can i cure this?


Regards,
Simon

Private Sub Worksheet_Activate()
Dim OriginalSheet
OriginalSheet = ActiveSheet.Name
If UsedRange.Row < Range("A1500") Then
Exit Sub
ElseIf UsedRange.Row >= Range("A1500") Then
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Range("A1:I1000").Select
Selection.Copy
Sheets("Summary Sheet").Select
Sheets.Add
ActiveSheet.Paste
Columns("A:I").Select
Columns("A:I").EntireColumn.AutoFit
Application.CutCopyMode = False
ActiveWindow.DisplayGridlines = False
ActiveSheet.Select
ActiveSheet.Tab.ColorIndex = 40
ActiveSheet.Name = "Summary Sheet" & " " & Date
Sheets(OriginalSheet).Select
Application.CutCopyMode = False
Range("A2:I1000").Select
Selection.Delete Shift:=xlUp
ActiveSheet.Protect
End If
Application.ScreenUpdating = True
Call SvSum
End Sub

> UsedRange.Row < Range("A1500")

The row property returns the first row number of the specified range.
UsedRange.Rows.Count would give you the last row number. Range("A1500")
refers to value of that cell (value is the default property of range objects).

Since you want the last row of Col A, I would suggest
Cells(Rows.Count, 1).End(xlup).Row < 1500

When rows are deleted, you'll get errors in dependent formulae. If you want
your formulae to refer to the new value that is in F2, try INDIRECT("F2").
Otherwise, all I could suggest is don't delete the cells (or clear the cells
and hide them by setting the hidden property to true).


"Simon Lloyd" wrote:

>
> Hi all, i am having trouble with the code below, i am trying to get the
> event to run if the UsedRange is >= Row 1500, i have tried just 1500
> instead of Range("A1500").............it seems that UsedRange.Row is
> always showing 1 even thought there are 2000 lines of data, i am only
> interested in triggering the event if the UsedRange is >=
> A1500..............Any ideas?, I am only interested in triggering the
> event if range("A1:A1500") is full the event must not trigger if any
> other column is filled up to or past this point, i have
> =VLOOKUP(F2,EventList,2,FALSE) and others in column "I" down to Row
> 3000 this is the reason i only wanted to trigger the event if the data
> existed in column A >=1500.
>
> One other problem i have is where i delete cells between A2:H1000 and
> move cells up the formulae i have in column "I" shows #REF! where it no
> longer references the cells i set i.e F2 how can i cure this?
>
>
> Regards,
> Simon
>
> Private Sub Worksheet_Activate()
> Dim OriginalSheet
> OriginalSheet = ActiveSheet.Name
> If UsedRange.Row < Range("A1500") Then
> Exit Sub
> ElseIf UsedRange.Row >= Range("A1500") Then
> Application.ScreenUpdating = False
> ActiveSheet.Unprotect
> Range("A1:I1000").Select
> Selection.Copy
> Sheets("Summary Sheet").Select
> Sheets.Add
> ActiveSheet.Paste
> Columns("A:I").Select
> Columns("A:I").EntireColumn.AutoFit
> Application.CutCopyMode = False
> ActiveWindow.DisplayGridlines = False
> ActiveSheet.Select
> ActiveSheet.Tab.ColorIndex = 40
> ActiveSheet.Name = "Summary Sheet" & " " & Date
> Sheets(OriginalSheet).Select
> Application.CutCopyMode = False
> Range("A2:I1000").Select
> Selection.Delete Shift:=xlUp
> ActiveSheet.Protect
> End If
> Application.ScreenUpdating = True
> Call SvSum
> End Sub
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=563555
>
>