Run Code on Activate Sheet

[Theresa]

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

 

[FSt1]

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]

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

 

[Gary Keramidas]

there are 2 end subs at the end, remove one of them.

 

[Dave Peterson]

And check your other thread.

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

 

[JLGWhiz]

Private Sub Worksheet_Activate()
Sub Deleteblankrows() <<<Delete this line

End Sub
End Sub <<<Delete the second End Sub

 

[JLGWhiz]

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.