Trouble using UsedRange to trigger an event??

[Simon Lloyd]

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

 

[Dove]

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

 

[RB Smissaert]

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

 

[Simon Lloyd]

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

 

[Dove]

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

 

[RB Smissaert]

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

 

[Simon Lloyd]

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