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

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"
wrote in message
...

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"
wrote in message
...

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 Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=561299

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"
wrote in message
...

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"
wrote in message
...

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


--
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