[RESOLVED] Excel 2013 - How to have the macro check if already executed

[akaVB]

Hi Guys,

I have run into a situation where folks at my end forget they have already executed the macro and click on it again.

Is there a way to incorporate a message box to pop up if they run the macro second time around?

This is the macro being run on raw data download. Firstly it checks for any rows with missing data and secondly it introduces two blank rows between each row of data.

Sub DeleteEmptyRowsInActiveSheet()

Application.ScreenUpdating = False

Dim lngI As Long

For lngI = ActiveCell.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1


If Application.WorksheetFunction.CountA(ActiveSheet.Rows(lngI)) = 0 Then

ActiveSheet.Rows(lngI).Delete

End If

Next



Cells.Replace What:="NULL", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

For x = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1

Cells(x, 1).Offset(1).EntireRow.Insert

Cells(x, 1).Offset(1).EntireRow.Insert

Next x



Application.ScreenUpdating = True



End Sub

[westconn1]

you can use a static variable within the procedure like

Sub DeleteEmptyRowsInActiveSheet()

Application.ScreenUpdating = False

Dim lngI As Long
static runalready
if not runalready then
runalready = true

For lngI = ActiveCell.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1


If Application.WorksheetFunction.CountA(ActiveSheet.Rows(lngI)) = 0 Then

ActiveSheet.Rows(lngI).Delete

End If

Next



Cells.Replace What:="NULL", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

For x = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1

Cells(x, 1).Offset(1).EntireRow.Insert

Cells(x, 1).Offset(1).EntireRow.Insert

Next x



Application.ScreenUpdating = True
else 
msgbox "this has already been run"

End Sub

this will work on a per session basis, when ever the workbook is opened runalready will be false
for a permanent method save some value to a cell or customdocumentproperty, then check that instead of the variable