Delete a blank cell and shift left

  • Thread starter Thread starter jrb
  • Start date Start date
J

jrb

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
 
Hi JRB

Here you go- have fun! =

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

Dim Blanks As Rang

Set Blanks = Cells.SpecialCells(xlCellTypeBlanks

Blanks.Delete shift:=xlToLef

End Su
 
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.
 
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
 
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
 
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 said:
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
Click to expand...
 
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
 
LOL - Good timing!! I just finished two weeks of hell at work, and am in a very
happy mood, so go ahead and send it on down :-)

ken.wright at ntlworld.com - obviously change the ' at ' bit
 
Definitely needed the sheet to understand it, but hopefully you like what I have
now sent you. Have gotten rid of all the interim steps, ditched the VBA, and is
all done by formulas. Takes a few seconds to calculate, but is fully automatic.

--
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 said:
LOL - Good timing!! I just finished two weeks of hell at work, and am in a very
happy mood, so go ahead and send it on down :-)

ken.wright at ntlworld.com - obviously change the ' at ' bit
Click to expand...
 
Back
Top