Hi,

This may do the trick. Call the function that follows like so (test before
running live):

Sub TestDelete()
Call RowsJoinData(5) '<where column 5 (E) is blank for every row to delete
end sub

Public Function RowsJoinData(argColumn As Integer)
'DELETES ENTIRE ROW WHERE CRITERIA CELL IS BLANK
Dim lX As Long
Dim lCount As Long
Application.ScreenUpdating = False
ActiveSheet.DisplayAutomaticPageBreaks = False
Application.Calculation = xlCalculationManual
lCount = ActiveSheet.UsedRange.Rows.Count
For lX = lCount To 1 Step -1
If Cells(lX, argColumn).FormulaR1C1 = "" Then
Rows(lX).EntireRow.Delete
End If
Next lX
Application.ScreenUpdating = True
ActiveSheet.DisplayAutomaticPageBreaks = True
Application.Calculation = xlCalculationAutomatic
End Function

HTH/

"Grd" wrote:

Hi

I have a 4000 row spreadsheet with approximately 33 columns and I have about
about 300 intermittent blank lines.

How can create a macro in the file to automate the process of deleting these
empty rows which I do manually?

I don't want to use the autofilter function to deal with this problem. I
would if possible to do this with a button or shortcut.

Anyone can help me this.

Thanks very much in advance
Janet

Worked great
Thanks very much

"quartz" wrote:

Hi,

This may do the trick. Call the function that follows like so (test before
running live):

Sub TestDelete()
Call RowsJoinData(5) '<where column 5 (E) is blank for every row to delete
end sub

Public Function RowsJoinData(argColumn As Integer)
'DELETES ENTIRE ROW WHERE CRITERIA CELL IS BLANK
Dim lX As Long
Dim lCount As Long
Application.ScreenUpdating = False
ActiveSheet.DisplayAutomaticPageBreaks = False
Application.Calculation = xlCalculationManual
lCount = ActiveSheet.UsedRange.Rows.Count
For lX = lCount To 1 Step -1
If Cells(lX, argColumn).FormulaR1C1 = "" Then
Rows(lX).EntireRow.Delete
End If
Next lX
Application.ScreenUpdating = True
ActiveSheet.DisplayAutomaticPageBreaks = True
Application.Calculation = xlCalculationAutomatic
End Function

HTH/

"Grd" wrote:

Hi

I have a 4000 row spreadsheet with approximately 33 columns and I have about
about 300 intermittent blank lines.

How can create a macro in the file to automate the process of deleting these
empty rows which I do manually?

I don't want to use the autofilter function to deal with this problem. I
would if possible to do this with a button or shortcut.

Anyone can help me this.

Thanks very much in advance
Janet