Solved: AutoRun the script

[Beatrix]

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]

In the ThisWorkbook Module put

[VBA]Private Sub Workbook_Open()
Call Loop_Example
End Sub
[/VBA]

Regards
Chris

[Kenneth Hobs]

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:
[VBA]Private Sub Workbook_Open()
Loop_Example
End Sub[/VBA]

[Beatrix]

Thanks very much..

[Beatrix]

Yep, sorry for missing VBA tags..

I appreciate for your help.

[Bob Phillips]

Yep, sorry for missing VBA tags..

Not much good apologising if you do nothing about it!

[Beatrix]

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]

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

[CatDaddy]

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

[Beatrix]

Ok, I will make sure to use VBA tags when I post a code next time. Many Thanks for the link.

[Beatrix]

Thank you for the tips and I like your signature saying "Happy Coding"

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

[Bob Phillips]

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]

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]

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.


[VBA]
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[/VBA]

[Bob Phillips]

Sorry, I didn't realise that. Because I have admin privileges, all posts always have the edit option.

Thanks for adding that.