HI,

I was wondering if there is Macro for this. I have a spreadsheet were
periodically
throughout column a the first cell is blank (the rest of the row
has Data but I don't need it. Does some one have a macro that will search for
these blank cells and delete the row?

Thanks
Mascot

will search for empty cells in column A, change if necessary..


Application.EnableEvents = False
Range("A1").Select
i = 1
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then
ActiveCell.EntireRow.Delete
End If
i = i + 1
Loop Until i = 500
Application.EnableEvents = True


Bit slow though, replace 500 for your maximum amount of cells.

like i said, bit slow, sure theres a faster way but this will work in
the meantime

Duncan


Mascot wrote:

HI,

I was wondering if there is Macro for this. I have a spreadsheet were
periodically
throughout column a the first cell is blank (the rest of the row
has Data but I don't need it. Does some one have a macro that will search for
these blank cells and delete the row?

Thanks
Mascot

oops...

replace "Application.EnableEvents" for "Application.ScreenUpdating"

so it looks like this..


Application.ScreenUpdating = False
Range("A1").Select
i = 1
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then
ActiveCell.EntireRow.Delete
End If
i = i + 1
Loop Until i = 40
Application.ScreenUpdating = True



Duncan





Duncan wrote:

will search for empty cells in column A, change if necessary..


Application.EnableEvents = False
Range("A1").Select
i = 1
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then
ActiveCell.EntireRow.Delete
End If
i = i + 1
Loop Until i = 500
Application.EnableEvents = True


Bit slow though, replace 500 for your maximum amount of cells.

like i said, bit slow, sure theres a faster way but this will work in
the meantime

Duncan


Mascot wrote:

HI,

I was wondering if there is Macro for this. I have a spreadsheet were
periodically
throughout column a the first cell is blank (the rest of the row
has Data but I don't need it. Does some one have a macro that will search for
these blank cells and delete the row?

Thanks
Mascot

This should do it just place it in a module and use a shortcut or button
to start it.....DO NOT change the range to Range("A:A") as it will run
the macro for ages trying to delete every row that statrs with a
blank!
Hope this helps
Regards
Simon

Sub Blankdelete()
Dim rng As Range
Dim mycell
Set rng = Range("A1:A40")
For Each mycell In rng
If mycell.Value = "" Then
mycell.EntireRow.Delete
End If
Next
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=564767

I'd do it manually.

Select column A
edit|goto|special|blanks
edit|delete|entire row

(or rightclick on one of the selected blank cells and delete|entire row)

Mascot wrote:

HI,

I was wondering if there is Macro for this. I have a spreadsheet were
periodically
throughout column a the first cell is blank (the rest of the row
has Data but I don't need it. Does some one have a macro that will search for
these blank cells and delete the row?

Thanks
Mascot

--

Dave Peterson

If the cells are actually blank and don't just look blank (contains a space
or a formula that returns a null string as examples that won't work)

Sub DeleteRows()
Dim rng as Range
On Error resume next
set rng = columns(1).specialcells(xlblanks)
On error goto 0
if not rng is nothing then
rng.EntireRow.Delete
End if
End sub

--
Regards,
Tom Ogilvy

"Mascot" wrote:

HI,

I was wondering if there is Macro for this. I have a spreadsheet were
periodically
throughout column a the first cell is blank (the rest of the row
has Data but I don't need it. Does some one have a macro that will search for
these blank cells and delete the row?

Thanks
Mascot

this seems to miss rows if there are two or more adjacent rows that have
blanks in column A.





--
Regards,
Tom Ogilvy


"Duncan" wrote:

oops...

replace "Application.EnableEvents" for "Application.ScreenUpdating"

so it looks like this..


Application.ScreenUpdating = False
Range("A1").Select
i = 1
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then
ActiveCell.EntireRow.Delete
End If
i = i + 1
Loop Until i = 40
Application.ScreenUpdating = True



Duncan





Duncan wrote:

will search for empty cells in column A, change if necessary..


Application.EnableEvents = False
Range("A1").Select
i = 1
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then
ActiveCell.EntireRow.Delete
End If
i = i + 1
Loop Until i = 500
Application.EnableEvents = True


Bit slow though, replace 500 for your maximum amount of cells.

like i said, bit slow, sure theres a faster way but this will work in
the meantime

Duncan


Mascot wrote:

HI,

I was wondering if there is Macro for this. I have a spreadsheet were
periodically
throughout column a the first cell is blank (the rest of the row
has Data but I don't need it. Does some one have a macro that will search for
these blank cells and delete the row?

Thanks
Mascot