About Us

Dave Peterson

I still think that trim() still would work. <vbg

pm wrote:

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.

"Dave Peterson" wrote:

If you're looking for a cell that looks blank (may contain multiple spaces):

If trim(Selection.Value) = "" 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

--

Dave Peterson

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Delete Blank Lines	Saxman	Excel Discussion (Misc queries)	3	January 9th 07 01:46 AM
a problem with blank lines	naughtyboy	Excel Worksheet Functions	1	August 8th 06 11:52 AM
Macro to insert blank lines	Terry Pinnell	Excel Discussion (Misc queries)	6	October 21st 05 11:21 PM
Blank lines in cells	Dan	Excel Discussion (Misc queries)	1	October 17th 05 03:51 PM
Delete Blank Lines	Charles	Excel Discussion (Misc queries)	3	August 8th 05 05:11 PM

Menu

About Us