Macro not recognizing blank lines as blank

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Don Guillett said:
As always, post your coding effort for comments.
Click to expand...

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
 
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
 
If you're looking for a cell that looks blank (may contain multiple spaces):

If trim(Selection.Value) = "" Then
 
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.
 
Thanks for your assistance.

Using If Trim 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.
 
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)
 
Dave - - That worked...thanks so much

Dave Peterson said:
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)
Click to expand...
 
I still think that trim() still would work. <vbg>
Thanks for your assistance.

Using If Trim 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.
Click to expand...
 
Back
Top