Trouble using UsedRange to trigger an event??

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?

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,

As long as the column A always has data if any other cells in the row have
data, try substituting:

In line 4 of your code: ActiveSheet.UsedRange.Rows.Count < 1500 for
"UsedRange.Row < Range("A1500")"

and

in line 6 of your code: ActiveSheet.UsedRange.Rows.Count >= 1500 for
"UsedRange.Row >= Range("A1500")"

David

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> 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?
>
> 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=561299
>

It looks you are after the last row of the used range.
3 ways to get that:

MsgBox ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count).Row

MsgBox (ActiveSheet.UsedRange.Cells(1).Row + _
ActiveSheet.UsedRange.Rows.Count) - 1

MsgBox Cells(1).SpecialCells(xlLastCell).Row

RBS


"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> 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?
>
> 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=561299
>

Thanks for your replies!, Dove i tried your proposed changes which does select what i want, the only problem is i have =VLOOKUP(F2,EventList,2,FALSE) and others in column I down to 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 the #REF! where it no longer references the cells i set i.e F2 how can i cure this?

RB thanks for your suggestions but i wasn't trying to find the last used row but use the row number to trigger the rest of the code.

Regards,
Simon

Simon,

If you have more rows used in some columns, you can use the following
function to get the used rows in a single column. It should be rather easy
to modify for a range of columns as well... Or to return the number of used
columns in a row or range of rows... It basically copies the column to a
temporary sheet, gets the used row count, deletes the temp sheet and returns
the number to where it was called from...

From what I can tell, Excel does not support getting the UsedRange rows or
columns from anything other than an entire worksheet object.

Regarding your question about the formula updating, the only way I know of,
off the top of my head, is to update them is to redefine them... The stuff
that I normally do in Excel doesn't involve deleting a range where formulas
are dependant upon the data there.

------------

Public Function GetUsedRowsInColumn()

Dim wsTemp As Worksheet
Dim wsCurrent As Worksheet
Dim lTemp As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set wsCurrent = ActiveSheet
Set wsTemp = Sheets.Add(Type:=xlWorksheet)

wsCurrent.Activate
Columns("A").Select
Selection.Copy

wsTemp.Activate
ActiveSheet.Paste

MsgBox ActiveSheet.UsedRange.Rows.Count
lTemp = ActiveSheet.UsedRange.Rows.Count

ActiveSheet.Delete

Set wsTemp = Nothing
Set wsCurrent = Nothing ' if done with this reference

Application.ScreenUpdating = True
Application.DisplayAlerts = True

GetUsedRowsInColumn = lTemp

End Function

-----------
"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Thanks for your replies!, Dove i tried your proposed changes which does
> select what i want, the only problem is i have
> =VLOOKUP(F2,EventList,2,FALSE) and others in column I down to 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 the #REF! where it
> no longer references the cells i set i.e F2 how can i cure this?
>
> RB thanks for your suggestions but i wasn't trying to find the last
> used row but use the row number to trigger the rest of the code.
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
> http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=561299
>

> but use the row number to trigger the rest of the code.

What exactly do you mean with: the row number?
I thought you wanted the row number of the last row of the used range. My
code will give that.

RBS

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Thanks for your replies!, Dove i tried your proposed changes which does
> select what i want, the only problem is i have
> =VLOOKUP(F2,EventList,2,FALSE) and others in column I down to 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 the #REF! where it
> no longer references the cells i set i.e F2 how can i cure this?
>
> RB thanks for your suggestions but i wasn't trying to find the last
> used row but use the row number to trigger the rest of the code.
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
> http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=561299
>

Sorry for the long delay in answering this thread, my aim in using the UsedRange is that if every cell in column A is used up to row 1500 then copy Range("A1:I1000"), Add a new sheet paste the information, then back to the source sheet Range("A3:I1000").Select clear all contents and move Range("A1001:I1500") up so that the formulas in column J dont show an error.

Or somethin like that!, my code works but doesn't focus on Column A for the used range which i should do and of course i have the problem with the formulae as previously mentioned in this thread!

Any ideas?

regards,
Simon