Originally Posted by
kmknowles
It still does not seem to be working with a larger data set. i have expanded the data set in the attached. Please let me know if you are having the same issues. It all worked out great in the earlier sample but for some reason will not work with the expanded data
run first this macro & wait.it's may be take little time.to clean......
Sub trim()
Dim LR As Long, c As Range
For Each c In Range("A1:EY200")
c.Value = Application.WorksheetFunction.Clean(Trim(c.Value))
Next c
End Sub
then run modytrane's macro it should work....
Sub removingblank()
Dim intCol As Long
Dim lastcol As Long
lastcol = Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
Worksheets("Sheet1").Range("A1:FZ400").Copy _
Destination:=Worksheets("Sheet2").Range("A1:FZ400")
Worksheets("Sheet2").Select
For intCol = 1 To lastcol
Range(Cells(1, intCol), Cells(200, intCol)). _
SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
Next intCol
End Sub
Bookmarks