PDA

View Full Version : Solved: erase blanks



neditheg
01-09-2009, 03:09 AM
Hi guys!

I've attached a pice of my spreadsheet. In that spreadsheet I want to erase the blanks cells.
I know one method : press F5 go to--> pess "special" --> select "blanks" --> and click "ok" ... but my excel containsa lot of data....and I get "selection is too large" --- so I think I need a vb code. Can somebody help me?

thank!

Bob Phillips
01-09-2009, 03:41 AM
It is likely that VBA will suffer the same restrictions as the methd that you describe.

And what exactly do you mean by erase blanks? To my mind, they are already erased.

neditheg
01-09-2009, 04:00 AM
It is likely that VBA will suffer the same restrictions as the methd that you describe.

And what exactly do you mean by erase blanks? To my mind, they are already erased.


ok i've attached again ... in the first sheet is what I have ... and in the second what I want :)

neditheg
01-09-2009, 04:26 AM
Sub delete_blanks()
Dim Rng As Range, MyCell As Range
Dim i As Long, r As Long
Dim C1 As String, C2 As String
For i = 3 To ActiveSheet.UsedRange.Columns.Count Step 3
C1 = ColumnLetter(i - 0)
C2 = ColumnLetter(i - 2)
For r = Range(C1 & Rows.Count).End(xlUp).Row To 1 Step -1
If Range(C1 & r).Value = "" Then
Range(C1 & r & ":" & C2 & r).Delete shift:=xlUp
End If
Next r
Next i
End Sub
Function ColumnLetter(ColumnNumber As Integer) As String
If ColumnNumber > 26 Then
ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
Chr(((ColumnNumber - 1) Mod 26) + 65)
Else
ColumnLetter = Chr(ColumnNumber + 64)
End If
End Function


I have this code .. but it doesn't work perfectly :|

Bob Phillips
01-09-2009, 05:00 AM
Sub DeleteEmptyCells()
Dim i As Long
Dim j As Long

Application.ScreenUpdating = False

For i = LastRow To 1 Step -1

For j = LastCol To 1 Step -1

If cells(i, j).Value = "" Then

cells(i, j).Delete shift:=xlShiftUp
End If
Next j
Next i

Application.ScreenUpdating = True

End Sub

'-----------------------------------------------------------------
Function LastRow() As Long
'-----------------------------------------------------------------
LastRow = cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End Function

'-----------------------------------------------------------------
Function LastCol() As Long
'-----------------------------------------------------------------
LastCol = cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End Function

neditheg
01-09-2009, 05:36 AM
thanks !!
it works !!

not 100% but i'll try to find the problem .

thanks again for ur time!