ExcelBanter - Macro not recognizing blank lines as blank

Dave - - That worked...thanks so much

"Dave Peterson" wrote:

First, if the contains a formula, it won't be empty.

Second, I'd change that formula (for future needs) to:

=IF(Input!H2="","",Input!H2)

(the +'s aren't needed and it usually makes life easier if you don't have to
test for a single space or multiple spaces.)

And if you're looking at a cell that could contain any number of spaces that you
want treated as blank, use:

if trim(selection.value) = "" then
(as long as the selection is a single cell)

pm wrote:

Thanks for your assistance.

Using isempty still doesn't delete the blank lines. The row inititally has
a formula in it : =IF(+Input!H2=""," ",+Input!H2) then I format it using:

Range("K8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

It will delete the lines if I clear the contents or hit the delete key in
the blank row, otherwise it's still not recognizing the row as blank.

Thanks.

"Barb Reinhardt" wrote:

THis line

If Selection.Value = " " Then

Is not looking for BLANKS. IT's looking for a cell with one space in it.

Your code could stand a lot of cleanup. I'm just going to give you a
change to this line to find the blanks.

if isempty(selection) then

"pm" wrote:

"Don Guillett" wrote:

As always, post your coding effort for comments.

--
Don Guillett
SalesAid Software

"pm" wrote in message
...
In the example below loc has two 4276 rows. I have a macro which deletes
the
blank rows, however, it's not recognizing the cell as blank. There's no
formula in the cell. In the macro I've formatted the rows to get rid of
the
formula. Any ideas?

Delivery Order
Location Empl# Employee Name Mt Order Date Date

Loc 1591 ALEXANDER, MARK IL

Loc 4276 DODD, KEITH RC 11089616 5/14/07
Loc 4276

Range("B8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

Range("N8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

Range("E8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

Dim nSheetRow As Long 'Current row being looked at
Dim bKeepGoing As Boolean 'Used to determine when to stop

bKeepGoing = True

nSheetRow = 6

Do While bKeepGoing
nSheetRow = nSheetRow + 1
sRange = "K" & nSheetRow
Range(sRange).Select

If Selection.Value = " " Then
sRange = "N" & nSheetRow
Range(sRange).Select
Selection.Copy

nSheetRow = nSheetRow - 1
sRange = "N" & nSheetRow
Range(sRange).Select
ActiveSheet.Paste

nSheetRow = nSheetRow + 1
sRange = "N" & nSheetRow
Range(sRange).Select
Application.CutCopyMode = False
Selection.EntireRow.Delete
nSheetRow = nSheetRow - 1
End If

sRange = "A" & (nSheetRow + 1)
Range(sRange).Select
If Selection.Value = "" Then
bKeepGoing = False
End If
Loop

Range("A1").Select
End Sub

--

Dave Peterson