Delete Rows when Blanks are found in Column A:A

Hi there,

I wish to write a simple macro to ask to find and delete the specific row
whenever
a blank is found in column A:A.

Can anyone help?

AJ

Hi AJ,

Try:

Public Sub DelBlanksInA()

Columns(1).SpecialCells(xlBlanks).EntireRow.Delete

End Sub


---
Regards,
Norman



"ddiicc" <[email protected]> wrote in message
news:[email protected]...
> Hi there,
>
> I wish to write a simple macro to ask to find and delete the specific row
> whenever
> a blank is found in column A:A.
>
> Can anyone help?
>
> AJ

Justin Labernne has aleady prepared this and some and oher functions as
addins

see this url

http://www.jlxl.net/Excel/downloads.html

see thea ddin <error remoer>

===================================
ddiicc <[email protected]> wrote in message
news:[email protected]...
> Hi there,
>
> I wish to write a simple macro to ask to find and delete the specific row
> whenever
> a blank is found in column A:A.
>
> Can anyone help?
>
> AJ

Thanks alot Norman

"Norman Jones" wrote:

> Hi AJ,
>
> Try:
>
> Public Sub DelBlanksInA()
>
> Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
>
> End Sub
>
>
> ---
> Regards,
> Norman
>
>
>
> "ddiicc" <[email protected]> wrote in message
> news:[email protected]...
> > Hi there,
> >
> > I wish to write a simple macro to ask to find and delete the specific row
> > whenever
> > a blank is found in column A:A.
> >
> > Can anyone help?
> >
> > AJ
>
>
>

Hi AJ,

Note, however, that this will fail if the data area of column A includes
more than 8192 non-contiguous areas of blank cells.

In fact, if this limit were to be exceeded, all rows would be deleted!
Fortunately, in normal use it is unlikely that you will encounter this
situation.

If the blank areas are likely to approach the 8192 limit, try instead:

'======================>>
Public Sub DelBlanksInA2()

Dim SH As Worksheet
Dim rng As Range
Dim i As Long, j As Long
Dim CalcMode As Long
Dim PgBreakMode
Const col As String = "A"

With Application
CalcMode = .Calculation
.Calculation = xlCalculationAutomatic
.ScreenUpdating = False
End With

Set SH = ActiveSheet '<<============== CHANGE

With SH
PgBreakMode = .DisplayPageBreaks
.DisplayPageBreaks = False
Set rng = .UsedRange
End With

j = rng.Rows(rng.Rows.Count).Row

For i = j To 1 Step -1
If IsEmpty(Cells(i, col)) Then
Rows(i).Delete
End If
Next i

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

SH.DisplayPageBreaks = PgBreakMode

End Sub

'<<====================

If the use of VBA is not mandatory, consider using the autofilter feature to
filter on blanks and then deleting the filtered rows.

As an alternative manual operation.

Select column A | Ctrl-g | Alt-s | k | OK | Alt-ed | Entire row | OK

(With the latter method, Excel, unlike VBA, will produce an error message if
the 8192 limit is encountered).


---
Regards,
Norman



"ddiicc" <[email protected]> wrote in message
news:[email protected]...
> Thanks alot Norman
>
> "Norman Jones" wrote:
>
>> Hi AJ,
>>
>> Try:
>>
>> Public Sub DelBlanksInA()
>>
>> Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
>>
>> End Sub
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "ddiicc" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi there,
>> >
>> > I wish to write a simple macro to ask to find and delete the specific
>> > row
>> > whenever
>> > a blank is found in column A:A.
>> >
>> > Can anyone help?
>> >
>> > AJ
>>
>>
>>