Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Code on Activate Sheet
I have this code which works great:
Sub Delete_blank_rows() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 11 EndRow = 500 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub Can you tell me how I can get this code to run when the sheet is made active? Theresa |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Code on Activate Sheet
hi
put the code in the worksheet activate event. right click the sheet tab then click view code. the worksheet change event will default. delete it. in the upper left combo box click worksheet. in the upper right combo box click and scroll to activate. be warned. the code will fire each time the sheet is activated. Regards FSt1 "Theresa" wrote: I have this code which works great: Sub Delete_blank_rows() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 11 EndRow = 500 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub Can you tell me how I can get this code to run when the sheet is made active? Theresa |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Code on Activate Sheet
Now I get an "Expected End Sub" compile error:
Private Sub Worksheet_Activate() Sub Deleteblankrows() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 11 EndRow = 500 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub End Sub "FSt1" wrote: hi put the code in the worksheet activate event. right click the sheet tab then click view code. the worksheet change event will default. delete it. in the upper left combo box click worksheet. in the upper right combo box click and scroll to activate. be warned. the code will fire each time the sheet is activated. Regards FSt1 "Theresa" wrote: I have this code which works great: Sub Delete_blank_rows() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 11 EndRow = 500 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub Can you tell me how I can get this code to run when the sheet is made active? Theresa |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Code on Activate Sheet
there are 2 end subs at the end, remove one of them.
-- Gary "Theresa" wrote in message ... Now I get an "Expected End Sub" compile error: Private Sub Worksheet_Activate() Sub Deleteblankrows() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 11 EndRow = 500 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub End Sub "FSt1" wrote: hi put the code in the worksheet activate event. right click the sheet tab then click view code. the worksheet change event will default. delete it. in the upper left combo box click worksheet. in the upper right combo box click and scroll to activate. be warned. the code will fire each time the sheet is activated. Regards FSt1 "Theresa" wrote: I have this code which works great: Sub Delete_blank_rows() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 11 EndRow = 500 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub Can you tell me how I can get this code to run when the sheet is made active? Theresa |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Code on Activate Sheet
And check your other thread.
Theresa wrote: I have this code which works great: Sub Delete_blank_rows() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 11 EndRow = 500 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub Can you tell me how I can get this code to run when the sheet is made active? Theresa -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Code on Activate Sheet
Private Sub Worksheet_Activate()
Sub Deleteblankrows() <<<Delete this line End Sub End Sub <<<Delete the second End Sub "Theresa" wrote: Now I get an "Expected End Sub" compile error: Private Sub Worksheet_Activate() Sub Deleteblankrows() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 11 EndRow = 500 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub End Sub "FSt1" wrote: hi put the code in the worksheet activate event. right click the sheet tab then click view code. the worksheet change event will default. delete it. in the upper left combo box click worksheet. in the upper right combo box click and scroll to activate. be warned. the code will fire each time the sheet is activated. Regards FSt1 "Theresa" wrote: I have this code which works great: Sub Delete_blank_rows() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 11 EndRow = 500 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub Can you tell me how I can get this code to run when the sheet is made active? Theresa |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Code on Activate Sheet
Like FSt1 wrote, be aware that this code will run each time you leave this
sheet and then return to it during a session. You might want to use the Workbook_Open method to run this. That way, it would only run once during a workbook session. You would set it up essentially the same as you did with Worksheet_Activate except you would use the ThisWorkbook code module instead of the Worksheet code module, and of course, use Workbook_Open instead of Worksheet_Activate. If this confuses you, forget I mentioned it. "Theresa" wrote: Now I get an "Expected End Sub" compile error: Private Sub Worksheet_Activate() Sub Deleteblankrows() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 11 EndRow = 500 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub End Sub "FSt1" wrote: hi put the code in the worksheet activate event. right click the sheet tab then click view code. the worksheet change event will default. delete it. in the upper left combo box click worksheet. in the upper right combo box click and scroll to activate. be warned. the code will fire each time the sheet is activated. Regards FSt1 "Theresa" wrote: I have this code which works great: Sub Delete_blank_rows() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 11 EndRow = 500 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub Can you tell me how I can get this code to run when the sheet is made active? Theresa |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run Code On Activate | Excel Worksheet Functions | |||
Activate code when sheet is deleted | Excel Programming | |||
Prevent code in "Sheet Activate" from running when sheet made visible from other macr | Excel Programming | |||
? activate a commandbutton in code ? | Excel Programming | |||
Return to Current Sheet in On (sheet activate) event macro | Excel Programming |