Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run Code On Activate
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.worksheet.functions
|
|||
|
|||
Run Code On Activate
Go to the VB-Editor, open the Project explorer.
Go to the worksheet module you want the code to work and enter this Private Sub Worksheet_Activate() Call Delete_blank_rows End Sub Hth Carlo On Dec 18, 11:41 am, 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.worksheet.functions
|
|||
|
|||
Run Code On Activate
hi again.
see your other post 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.worksheet.functions
|
|||
|
|||
Run Code On Activate
Thanks Carlo.....it works great!
"carlo" wrote: Go to the VB-Editor, open the Project explorer. Go to the worksheet module you want the code to work and enter this Private Sub Worksheet_Activate() Call Delete_blank_rows End Sub Hth Carlo On Dec 18, 11:41 am, 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.worksheet.functions
|
|||
|
|||
Run Code On Activate
See ALL of your other posts.
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.worksheet.functions
|
|||
|
|||
Run Code On Activate
Some folks here take periodic breaks from the keyboard. Please do the same.
"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 | |||
Activate object | Excel Worksheet Functions | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
what is code to activate a combo box in control toolbox | Excel Discussion (Misc queries) | |||
When to Activate? | Excel Discussion (Misc queries) | |||
How to activate "New" tab in Dataform through VB Code | Excel Discussion (Misc queries) |