Run Code On Activate

T

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
 
C

carlo

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
 
T

Theresa

Thanks Carlo.....it works great!

carlo said:
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
Click to expand...
 
D

Dave Peterson

See ALL of your other posts.

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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Deleting rows (with zeros) with a macro 7
Run Code on Activate Sheet 6
Lookup : Syntax Error 0
Newb: Asking about a tweak in code 2
VBA - (re-) Set a Table Style 2
delete rows if cell in row contains "a" or "o" or empty 2
Help revising slow code 2
Code fails when sheet is protected 3

Top