Delete rows
 

Hello,

From cell A1 to A3500, I have many cells with infos in it and many cells
with no info at all. How can I create a macro that will delete the entire
row in which colomn A has no info???

In example:

A1: Test 1
A2:
A3:
A4:
A5: Test 2
A6:
A7: Test 3
A8:

I would like to create a macro that will automatically delete rows 2-3-4-6-8
and leave intact rows 1-5-7.

Thanks!!!

Delete rows
 

Try this -- it works well for me:

Private Sub DeleteBlankRows()

Dim lastrow As Long
Dim r As Long
lastrow = Range("C" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
ActiveSheet.Rows(r).Delete
End If
Next

End Sub

HTH
"One-Leg" wrote in message
...
Hello,

From cell A1 to A3500, I have many cells with infos in it and many cells
with no info at all. How can I create a macro that will delete the entire
row in which colomn A has no info???

In example:

A1: Test 1
A2:
A3:
A4:
A5: Test 2
A6:
A7: Test 3
A8:

I would like to create a macro that will automatically delete rows
2-3-4-6-8
and leave intact rows 1-5-7.

Thanks!!!

Delete rows
 

Sub DeleteRowsWithBlanks()
Range("A:A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

HTH,
Bernie
MS Excel MVP


"One-Leg" wrote in message
...
Hello,

From cell A1 to A3500, I have many cells with infos in it and many cells
with no info at all. How can I create a macro that will delete the entire
row in which colomn A has no info???

In example:

A1: Test 1
A2:
A3:
A4:
A5: Test 2
A6:
A7: Test 3
A8:

I would like to create a macro that will automatically delete rows 2-3-4-6-8
and leave intact rows 1-5-7.

Thanks!!!

Delete rows
 

Your "modified code" will look like this:

Private Sub DeleteBlankRows()

Dim lastrow As Long
Dim r As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.CountIf(Cells(r, "A").Resize(1, 1), "") = 1 Then
ActiveSheet.Rows(r).Delete
End If
Next

End Sub
:)
"One-Leg" wrote in message
...
Hello,

From cell A1 to A3500, I have many cells with infos in it and many cells
with no info at all. How can I create a macro that will delete the entire
row in which colomn A has no info???

In example:

A1: Test 1
A2:
A3:
A4:
A5: Test 2
A6:
A7: Test 3
A8:

I would like to create a macro that will automatically delete rows
2-3-4-6-8
and leave intact rows 1-5-7.

Thanks!!!

Delete rows
 

Bernie,

I like your code better -- quick and to-the-point! ;)

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Sub DeleteRowsWithBlanks()
Range("A:A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

HTH,
Bernie
MS Excel MVP


"One-Leg" wrote in message
...
Hello,

From cell A1 to A3500, I have many cells with infos in it and many cells
with no info at all. How can I create a macro that will delete the
entire
row in which colomn A has no info???

In example:

A1: Test 1
A2:
A3:
A4:
A5: Test 2
A6:
A7: Test 3
A8:

I would like to create a macro that will automatically delete rows
2-3-4-6-8
and leave intact rows 1-5-7.

Thanks!!!

Delete rows
 

You have been given a choice of macros, but could do the job manually.

Select column A and F5SpecialBlanksOK

EditDeleteEntire row.


Gord Dibben MS Excel MVP

On Fri, 8 Dec 2006 08:41:00 -0800, One-Leg
wrote:

Hello,

From cell A1 to A3500, I have many cells with infos in it and many cells
with no info at all. How can I create a macro that will delete the entire
row in which colomn A has no info???

In example:

A1: Test 1
A2:
A3:
A4:
A5: Test 2
A6:
A7: Test 3
A8:

I would like to create a macro that will automatically delete rows 2-3-4-6-8
and leave intact rows 1-5-7.

Thanks!!!