How can I Delete rows were the first cell is blank?
 

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

How can I Delete rows were the first cell is blank?
 

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

How can I Delete rows were the first cell is blank?
 

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

How can I Delete rows were the first cell is blank?
 

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

How can I Delete rows were the first cell is blank?
 

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

How can I Delete rows were the first cell is blank?
 

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

How can I Delete rows were the first cell is blank?
 

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