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

Delete Rows when Blanks are found in Column A:A
 

Hi AJ,

Try:

Public Sub DelBlanksInA()

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

End Sub


---
Regards,
Norman



"ddiicc" wrote in message
...
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

Delete Rows when Blanks are found in Column A:A
 

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 wrote in message
...
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

Delete Rows when Blanks are found in Column A:A
 

Thanks alot Norman

"Norman Jones" wrote:

Hi AJ,

Try:

Public Sub DelBlanksInA()

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

End Sub


---
Regards,
Norman



"ddiicc" wrote in message
...
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

Delete Rows when Blanks are found in Column A:A
 

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" wrote in message
...
Thanks alot Norman

"Norman Jones" wrote:

Hi AJ,

Try:

Public Sub DelBlanksInA()

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

End Sub


---
Regards,
Norman



"ddiicc" wrote in message
...
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