Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to delete blank cells between those with entries | Excel Worksheet Functions | |||
How do I Delete blank cells in a row | Excel Discussion (Misc queries) | |||
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete | Excel Programming | |||
Using a function to delete blank cells | Excel Worksheet Functions | |||
Macro to delete all blank cells | Excel Programming |