xlCellTypeBlanks, but no blank cells to delete
 

As part of importing a csv file, I have to correct some errors in the data.
What I had before worked OK, but this weeks version of the csv made my code
bomb out.

What I'm doing is deleting some blank cells. This week, there were no blank
cells in the csv!

This is what I have now:

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift=xlToLeft

What I need is something to do this:
Selection.SpecialCells(xlCellTypeBlanks).Select
If there are no blank cells, jump past the next line
Selection.Delete Shift=xlToLeft
to here

HELP PLEASE!

xlCellTypeBlanks, but no blank cells to delete
 

How about something like this

Dim myRange as range
Set myRange = Nothing
on error resume next
set myrange = Selection.SpecialCells(xlCellTypeBlanks)
on error goto 0
if not myrange is nothing then
myrange.Delete Shift=xlToLeft
end if
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Melvin Purvis" wrote:

As part of importing a csv file, I have to correct some errors in the data.
What I had before worked OK, but this weeks version of the csv made my code
bomb out.

What I'm doing is deleting some blank cells. This week, there were no blank
cells in the csv!

This is what I have now:

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift=xlToLeft

What I need is something to do this:
Selection.SpecialCells(xlCellTypeBlanks).Select
If there are no blank cells, jump past the next line
Selection.Delete Shift=xlToLeft
to here

HELP PLEASE!

xlCellTypeBlanks, but no blank cells to delete
 

Count before Selecting:

If Selection.SpecialCells(xlCellTypeBlanks).Count0 then
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift=xlToLeft
End If
--
Gary''s Student - gsnu2007k


"Melvin Purvis" wrote:

As part of importing a csv file, I have to correct some errors in the data.
What I had before worked OK, but this weeks version of the csv made my code
bomb out.

What I'm doing is deleting some blank cells. This week, there were no blank
cells in the csv!

This is what I have now:

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift=xlToLeft

What I need is something to do this:
Selection.SpecialCells(xlCellTypeBlanks).Select
If there are no blank cells, jump past the next line
Selection.Delete Shift=xlToLeft
to here

HELP PLEASE!

xlCellTypeBlanks, but no blank cells to delete
 

Sub Shift_Blanks()
Dim c As Range
With Selection
Do
Set c = .Find("", LookIn:=xlValues, lookat:=xlWhole, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.Delete shift:=xlToLeft
Loop
End With

'do the rest of your stuff

End Sub


Gord Dibben MS Excel MVP

On Tue, 16 Dec 2008 18:31:43 -0600, Melvin Purvis < wrote:

As part of importing a csv file, I have to correct some errors in the data.
What I had before worked OK, but this weeks version of the csv made my code
bomb out.

What I'm doing is deleting some blank cells. This week, there were no blank
cells in the csv!

This is what I have now:

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift=xlToLeft

What I need is something to do this:
Selection.SpecialCells(xlCellTypeBlanks).Select
If there are no blank cells, jump past the next line
Selection.Delete Shift=xlToLeft
to here

HELP PLEASE!

xlCellTypeBlanks, but no blank cells to delete
 

Many thanks to all who posted with replies. All solutions accomplished the job,
showing there is more than one way to skin a cat. Thanks again!




?B?QmFyYiBSZWluaGFyZHQ=?=
wrote:
How about something like this

Dim myRange as range
Set myRange = Nothing
on error resume next
set myrange = Selection.SpecialCells(xlCellTypeBlanks)
on error goto 0
if not myrange is nothing then
myrange.Delete Shift=xlToLeft
end if
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Melvin Purvis" wrote:

As part of importing a csv file, I have to correct some errors in the data.
What I had before worked OK, but this weeks version of the csv made my code
bomb out.

What I'm doing is deleting some blank cells. This week, there were no blank
cells in the csv!

This is what I have now:

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift=xlToLeft

What I need is something to do this:
Selection.SpecialCells(xlCellTypeBlanks).Select
If there are no blank cells, jump past the next line
Selection.Delete Shift=xlToLeft
to here

HELP PLEASE!