PDA

View Full Version : Solved: AutoRun the script



Beatrix
03-22-2012, 09:05 AM
Hi Everyone ,

I need to run below script automatically as soon as the spreadsheet is open. How do we do that? I appreciate for your help.

Cheers
Yeliz


"http://www.rondebruin.nl/delete.htm

Sub Loop_Example()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With Sheets("Summary")

'We select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1

'We check the values in the B column in this example
With .Cells(Lrow, "B")

If Not IsError(.Value) Then

If .Value = 0 Then .EntireRow.Delete
'This will delete each row with the Value 0
'in Column B, case sensitive.

End If

End With

Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

wrightyrx7
03-22-2012, 09:18 AM
In the ThisWorkbook Module put

Private Sub Workbook_Open()
Call Loop_Example
End Sub


Regards
Chris

Kenneth Hobs
03-22-2012, 09:21 AM
Please post code between VBA code tags. Obviously, your routine would be in a Module.

In the VBE, doubleclick ThisWorkbook object in the Project Explorer (Ctrl+R) and paste:
Private Sub Workbook_Open()
Loop_Example
End Sub

Beatrix
03-22-2012, 09:30 AM
Thanks very much..

Beatrix
03-22-2012, 09:31 AM
Yep, sorry for missing VBA tags..

I appreciate for your help.

Bob Phillips
03-22-2012, 09:47 AM
Yep, sorry for missing VBA tags..

Not much good apologising if you do nothing about it!

Beatrix
03-22-2012, 10:06 AM
I get your point xld. I just copied the code from
http://www.rondebruin.nl/delete.htm so didn't know how to add VBA code tags. If you could tell me how to do it then sure I will do something about it when I post next thread. I've checked FAQ but couldn't find it there.

Kenneth Hobs
03-22-2012, 10:19 AM
This forum is special in that you use VBA code tags where others use CODE. See the FAQ's link at: http://www.vbaexpress.com/forum/faq.php?faq=vb_read_and_post#faq_vb_special_codes

CatDaddy
03-22-2012, 10:33 AM
hit the little vba button and put your code between the tags that appear "VBA" and "/VBA"

Beatrix
03-22-2012, 10:37 AM
Ok, I will make sure to use VBA tags when I post a code next time. Many Thanks for the link.

Beatrix
03-22-2012, 10:47 AM
Thank you for the tips and I like your signature saying "Happy Coding":hi:




hit the little vba button and put your code between the tags that appear "VBA" and "/VBA"

Bob Phillips
03-22-2012, 12:13 PM
Ok, I will make sure to use VBA tags when I post a code next time.

Edit this thread and add them, people may still look at the thread.

Kenneth Hobs
03-22-2012, 12:34 PM
The ability to edit a post now after longer than 5 or 10 minutes now is good.

I often edit my posts because after I read it again after posting I say, "what kind of grammar is that, you went to college"?

Beatrix
03-22-2012, 01:10 PM
Edit this thread and add them, people may still look at the thread.

I think there is a time limit as I can't edit it now:( but I re-posted the script below by adding VBA tags.



Sub Loop_Example()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With Sheets("Summary")

'We select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1

'We check the values in the B column in this example
With .Cells(Lrow, "B")

If Not IsError(.Value) Then

If .Value = 0 Then .EntireRow.Delete
'This will delete each row with the Value 0
'in Column B, case sensitive.

End If

End With

Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

Bob Phillips
03-22-2012, 04:01 PM
Sorry, I didn't realise that. Because I have admin privileges, all posts always have the edit option.

Thanks for adding that.