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

[Mascot]

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

[Duncan]

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

[Duncan]

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

[Simon Lloyd]

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

[Dave Peterson]

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

[Tom Ogilvy]

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
>

[Tom Ogilvy]

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
>
>