Why does this code take so long to execute.

[Guest]

' Delete all lines
Dim lin As Line
For Each lin In ActiveSheet.Lines
If lin.Name Like "Line*" Then
lin.Delete
End If
Next

The longer I have worked on the project with this code, the more sluggish
this seemed to be. Each time I run the macro with this code It will create
300-500 line segments. This delete code is the 1st thing that is done to
clear out all lines from the previous execution and then re-draw the lines
based on current data. The code that draws the lines has much more meat to
its code, (lots of loops and conversions) but it runs very fast (a matter of
seconds). As I step through the code (single step), the cursor seems to hang
on the lin.Delete for about 1-3 seconds, Of course watching the execution
i'm sure takes significantly longer, but I have seen this macro take about
3-5 minutes to complete it's deleting of all the lines.

As the lines are being created I question the autonumber that is generated,
Line15023 for example, I am wondering if that might be causing some overhead.


Is there a more efficient way to delete all lines?
I have also added code to turn off "calculation" before the deletes and
turning recalc back on afterwards, but that didnt seem to help.

 

[Tom Ogilvy]

Activesheet.Lines.Delete

 

[Guest]

Excelent! that worked in the blink of an eye,
Thanks Tom!

Activesheet.Lines.Delete

 

[Xlyr]

Hey there,

I'm a HUGE novice with excel programming, through self-teaching I've covered
quite a few simple commands but still there are loads to learn - yay.

Anyhoo, I want to be able to control what sheets some of my staff @ work
have access to.
I can easily make the tabs hidden, but unfortunately most of my staff aren't
that simple!

What I have so far is a simple userform system which checks payroll numbers
and passwords - and their access privilege...
What I want to be able to do is say... give level 1 users access to a single
"staff" sheet...
Give level 2 users access to "staff" and "holiday" sheets...
give level 3 users access to "staff", "holiday" and "reports" sheets...

I am aware of the Visible, Hidden and VeryHidden variables for sheets - how
to use them is the issue...

I keep coming up with ideas like...

If user_payroll.Value = Reference("PAYROLL1") Then
password_form.Show
Else
MsgBox("Payroll not recognised."), , "Oops!"
----------------------------------------------------------------------------------------------
If user_pass.Value = Reference("PASSWORD1") Then
Make Sheets("staff").Visible
Else
MsgBox("Password not recognised."), , "Oops!"


Then on logging out/closing the spreadsheet, the sheet(s) would become
VeryHidden again...

Any ideas how I can make this work - like I said I'm self taught and I can't
find any good learning resources for this - the books I have on excel VBA
don't mention anything like this.

All input _WILL_ be greatly appreciated!

Also - any ideas on how I could instead create a similar system by using an
excel spreadsheet as a database then taking all the login details from there
would be excellent.

thanks,

Kai

 

[Tom Ogilvy]

Not sure why you are posting this in this thread, but see a possible
approach in response to your first posting.