I need to figure out a way to take a range, and for each blank cell i
that range delete it and shuft it left. Is there an easy way to d
this?


Thanks JR

--
Message posted from http://www.ExcelForum.com

Hi JRB

Here you go- have fun! =

------------------------------------------------------------
Sub DelBlanks(

Dim Blanks As Rang

Set Blanks = Cells.SpecialCells(xlCellTypeBlanks

Blanks.Delete shift:=xlToLef

End Su
------------------------------------------------------------

SuperJas.

If you didn't need it in code, then just select the range, do Edit / Go To /
Special / Blank cells / then Edit / Delete / Shift Cells left.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"jrb " wrote in message
...
I need to figure out a way to take a range, and for each blank cell in
that range delete it and shuft it left. Is there an easy way to do
this?


Thanks JRB


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.595 / Virus Database: 378 - Release Date: 25/02/2004

Ken,

Thanks for your suggestion but when I set a range, select goto
special, blanks it doesn't select any of the blank cells. Any ideas

--
Message posted from http://www.ExcelForum.com

Then they aren't really blank. This means that the code you were given by the
others also won't work. If you are seeing 'blank' cells, but for example those
cells are really the results of formula that return blanks, eg

=IF(X<1,"","abc") which if X doesn't equal 1 will appear to be a blank cell.
The cell however is not really blank, and so you cannot use the special cells
method to do what you want. Perhaps also you actually have spaces in there. If
so then what you can do is to use a piece of code to delete what appear to be
blank rows by trimming the cells and deleting any that have a length of 0, eg:-

Sub DelBlankLookingCells()

Dim Rng As Range
Dim Cel As Range
Dim DelRng As Range
Set DelRng = Nothing
Set Rng = ActiveSheet.UsedRange

For Each Cel In Rng
If Len(Trim(Cel.Value)) = 0 Then
If DelRng Is Nothing Then
Set DelRng = Cel
Else
Set DelRng = Union(DelRng, Cel)
End If
End If
Next
If Not DelRng Is Nothing Then
DelRng.Delete Shift:=xlToLeft
End If
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"jrb " wrote in message
...
Ken,

Thanks for your suggestion but when I set a range, select goto,
special, blanks it doesn't select any of the blank cells. Any ideas.


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.595 / Virus Database: 378 - Release Date: 25/02/2004

blank rows by trimming

I meant cells, not rows :-(

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Ken Wright" wrote in message
...
Then they aren't really blank. This means that the code you were given by the
others also won't work. If you are seeing 'blank' cells, but for example
those
cells are really the results of formula that return blanks, eg

=IF(X<1,"","abc") which if X doesn't equal 1 will appear to be a blank cell.
The cell however is not really blank, and so you cannot use the special cells
method to do what you want. Perhaps also you actually have spaces in there.
If
so then what you can do is to use a piece of code to delete what appear to be
blank rows by trimming the cells and deleting any that have a length of 0,
eg:-

Sub DelBlankLookingCells()

Dim Rng As Range
Dim Cel As Range
Dim DelRng As Range
Set DelRng = Nothing
Set Rng = ActiveSheet.UsedRange

For Each Cel In Rng
If Len(Trim(Cel.Value)) = 0 Then
If DelRng Is Nothing Then
Set DelRng = Cel
Else
Set DelRng = Union(DelRng, Cel)
End If
End If
Next
If Not DelRng Is Nothing Then
DelRng.Delete Shift:=xlToLeft
End If
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"jrb " wrote in message
...
Ken,

Thanks for your suggestion but when I set a range, select goto,
special, blanks it doesn't select any of the blank cells. Any ideas.


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.595 / Virus Database: 378 - Release Date: 25/02/2004




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.595 / Virus Database: 378 - Release Date: 25/02/2004

Ken,

Is it possible for me to send you a copy of this spreadsheet I a
working on. I have one other item that I am trying to accomplish, bu
it will be difficult to explain without you seeing the sheet.

Jef

--
Message posted from http://www.ExcelForum.com