I have a worksheet with a column named DATE. Some of the date cells are
empty. I want a macro to delete all of the rows in which the date cell is
empty.


Gene Augustin

Try after changing A in the second row to the column you having DATE

Sub deleteRow()
ColumntoDelete = "A"
For i = Cells(Rows.Count, ColumntoDelete).End(xlUp).Row To 1 Step -1
If Cells(i, ColumntoDelete) = "" Then Cells(i, "A").EntireRow.Delete
Next i
End Sub

"Gene Augustin" wrote:

I have a worksheet with a column named DATE. Some of the date cells are
empty. I want a macro to delete all of the rows in which the date cell is
empty.


Gene Augustin

Hi,

Here is code to delete all rows which have blank cells in column A. You can
modify this to delete all cells of many different types and for any column.

Sub DeleteRows()
Range("A1:A" &
Range("A65536").End(xlUp).Row).SpecialCells(xlCell TypeBlanks).EntireRow.Delete
End Sub

The advantage of this type of code is it uses Excel's built in feature and
therefor runs very fast, in fact, between 60-100 times faster than loops.

In this case I am deleting the entire row but you can replace the
..EntireRow.Delete with .Delete Shift:=XLUp

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Gene Augustin" wrote:

I have a worksheet with a column named DATE. Some of the date cells are
empty. I want a macro to delete all of the rows in which the date cell is
empty.


Gene Augustin

Place a control box on the sheet from the control box.
Double click the command button to open code window and paste following codes


Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com

Chris Bode;260396 Wrote:
Place a control box on the sheet from the control box.
Double click the command button to open code window and paste following
codes

Code:
--------------------

Private Sub CommandButton1_Click()
Dim row As Integer, col As Integer
row = 1
col = 1

For row = 1 To 100
If Sheet1.Cells(row, col).Value = "" Then
Sheet1.Rows(row).Delete
End If
Next
End Sub

--------------------




Chris
------
Convert your Excel spreadsheet into an online calculator.
'SpreadsheetConverter - Convert Excel into live and calculating web
pages' (http://www.spreadsheetconverter.com)




--
Chris Bode

Deleting like that is not good practice, when deleting you should
delete from last to first as per Sheeloo's answer!


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=72639