PDA

View Full Version : Solved: Removing Blanks



Sir Babydum GBE
01-12-2007, 02:37 AM
Hi,

I know that i can remove blanks by using GoTo/Blanks/Delete/Shift Right (or up),

But is it possible to remove blanks from a block of cells (say from columns A to G) but not have the info from columns H onwards shift left?

Or otherwise select an area, and any cells out of the highlighted don't shift.

Thanks

Bob Phillips
01-12-2007, 03:22 AM
Do you mean just clear them, or shift them left then re-insert however many celsl you shifted (the latter would need VBA)?

Sir Babydum GBE
01-12-2007, 03:31 AM
I mean the latter I think...

So say I have text or formulas in cells a1, c1, and f1. I highlight a1 to e1 and perform this trick I'm asking for. What should happen is that b1 (also, technically, d1 and e1) are deleted and shifted so that now the text is in a1 and b1 - but because f1 was outside of the highlighted range, it doesn't get shifted left.

Does this make sense?

Bob Phillips
01-12-2007, 04:50 AM
Public Sub Test()
Dim cell As Range
Dim rng As Range
Dim i As Long
Dim cnt As Long

Set rng = Selection.SpecialCells(xlCellTypeBlanks)
For i = Selection.Columns.Count + Selection.Column - 1 To Selection.Column Step -1
If Cells(Selection.Row, i).Value = "" Then
Cells(Selection.Row, i).Delete Shift:=xlToLeft
cnt = cnt + 1
End If
Next i
If cnt > 0 Then _
Cells(Selection.Row, Selection.Column + Selection.Columns.Count - cnt) _
.Resize(, cnt).Insert Shift:=xlToRight
End Sub

mdmackillop
01-12-2007, 05:03 AM
A slight variation
Sub SelDelete()
Dim Endd As String, i As Long, j As Long

Endd = Split(Selection.Address, ":")(1)
For i = Selection.Cells.Count To 1 Step -1
If Len(Selection(i)) = 0 Then
Selection(i).Delete xlToLeft
j = j + 1
End If
Next
Range(Endd).Offset(, -j + 1).Resize(, j).Insert xlToRight
End Sub

Sir Babydum GBE
01-12-2007, 05:04 AM
Thanks so much XLD,

That's working well. However, though I see that your code says "Set rng = Selection...", I notice that it only works on one row at a time - no matter many rows are selected.

Did you expect that result?

Can it work on multiple rows.

Basically what I have is a list a few thousand rows deep. part of the list is dedicated to descriptive keywords - and when amendments are made, some keywords are taken out. So periodically i must find and delete hundreds or thousands of blanks for sheet housekeeping.

Sir Babydum GBE
01-12-2007, 05:10 AM
Thanks mdmackillop

This seems to work only in the same was as it would if i did edit/goto/find/delete.

Everything was shifted to the left regardless of whether it was in the selected area or not.

mdmackillop
01-12-2007, 05:11 AM
Was already working on that, but maybe a bit slow for 1000s
Sub SelDelete()
Dim Endd As String, i As Long, j As Long, ThisRw As Range
Application.ScreenUpdating = False
For Each rw In Selection.Rows
Set ThisRw = Intersect(rw, Selection)
Endd = Split(ThisRw.Address, ":")(1)
For i = ThisRw.Cells.Count To 1 Step -1
If Len(ThisRw(i)) = 0 Then
ThisRw(i).Delete xlToLeft
j = j + 1
End If
Next
Range(Endd).Offset(, -j + 1).Resize(, j).Insert xlToRight
j = 0
Next
Application.ScreenUpdating = True
End Sub

Bob Phillips
01-12-2007, 05:13 AM
BD,

That line is my original thinking on it, I should have deleted it.

The problem is identifying not blank cells and deleting them, knoiwing how many are deleted so as to re-instate those blanks at the end.

From your original post I thought that you would be selecting different sized areas. As you are now saying you will have many rows, how will they be identified, a selection, usedrange, code to determine from say column A , or something else?

Either way, I think a loop is requiured, which will be quite slow (but faster than manual).

Sir Babydum GBE
01-12-2007, 05:19 AM
BD,

That line is my original thinking on it, I should have deleted it.

The problem is identifying not blank cells and deleting them, knoiwing how many are deleted so as to re-instate those blanks at the end.

From your original post I thought that you would be selecting different sized areas. As you are now saying you will have many rows, how will they be identified, a selection, usedrange, code to determine from say column A , or something else?

Either way, I think a loop is requiured, which will be quite slow (but faster than manual).

Well, the reason I mention using the selected area rather than specifing a selection is that my list is always changing. But as to it being slow - that's not a big deal in this case - I'd be happy to run it and leave it work for a couple of minutes. As you say - still tons faster and less frought with danger than manually doing it.

I guess I could name my range "KeyWords" and change the range area as and when appropriate.

Thanks again both

mdmackillop
01-12-2007, 05:22 AM
I tried 3,000 rows and it got there eventually. I'll give it some further thought.

Bob Phillips
01-12-2007, 05:25 AM
Here is a solution based upon selecting the data to be compressed



Public Sub Test()
Dim oRow As Range
Dim rng As Range
Dim iStartCol As Long
Dim iLastCol As Long
Dim cColumns As Long
Dim i As Long
Dim cnt As Long

Application.ScreenUpdating = False

Set rng = Selection 'ActiveSheet.UsedRange
iStartCol = rng.Column
cColumns = rng.Columns.Count

For Each oRow In rng.Rows

cnt = 0
iLastCol = Cells(oRow.Row, Columns.Count).End(xlToLeft).Column

If iLastCol > cColumns + iStartCol - 1 Then

For i = cColumns + iStartCol - 1 To iStartCol Step -1
If Cells(oRow.Row, i).Value = "" Then
Cells(oRow.Row, i).Delete shift:=xlToLeft
cnt = cnt + 1
End If
Next i
If cnt > 0 Then _
Cells(oRow.Row, iStartCol + cColumns - cnt) _
.Resize(, cnt).Insert shift:=xlToRight

End If

Next oRow

Application.ScreenUpdating = True

End Sub

Bob Phillips
01-12-2007, 05:30 AM
5,000 rows, very sparse , 1.5 secs.

Sir Babydum GBE
01-12-2007, 06:09 AM
Well both of those solutions work well. Question is, which one do I use without upsetting the other?

I know - I'll take the first ten lines of one, and the last ten of the other.

Perfect!

Thanks both.

mdmackillop
01-12-2007, 06:14 AM
HTH, and I don't think any upset happens in these situations.

How about alternate lines?

Bob Phillips
01-12-2007, 06:28 AM
Random?

johnske
01-12-2007, 07:03 PM
Why not both? :)

Randomize
If Int((2 * Rnd)) = 0 Then
Call SelDelete
Else
Call Test
End If

Brandtrock
01-13-2007, 12:36 AM
Why not both? :)

Randomize
If Int((2 * Rnd)) = 0 Then
Call SelDelete
Else
Call Test
End If


I should think that an intergalactic diplomat of BD's stature would very much appreciate this solution.

Sir Babydum GBE
01-19-2007, 10:39 AM
I should think that an intergalactic diplomat of BD's stature would very much appreciate this solution.Hmm, does this work accoring to the Babyduminiam Math Model?