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