How to remove empty space, unable to make pvot, because pvot calculate empty space

I HAVE MANY EMPTY CELL IN RANDOMLY. HOW TO REMOVE THAT, ALSO I DON'T WANT REMOVE SPACE BETWEEN TWO WORDS. JUST I WANT TO CLEAN EMPTY CELLS.. I CANT UNABLE TO MAKE PVOT TABLE. BECAUSE PVOT CALCULATE EMPTY CELL AS VALUE,

SEE MY EXAMPLE FILE, B COLUMN IS JUST APPEAR AS EMPTY CELL, BUT TRUTH IS B IS NOT EMPTY, HOW TO SOLVE.

I HAVE MANY DATA'S LIKE THIS IN SINGLE EXCEL, JUST I GIVEN SAMPLE EXCEL IN THIS THREAD..

Perhaps a macro like this to "trim" column B?

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("B" & i)
        .Value = WorksheetFunction.Trim(.Value)
    End With
Next i
End Sub

Alf

i don't know about macro.. is there another way ?>...

yes alansidman, i tried many time time macro, i coudn't understand, but now i tried as you said, its working, i want learn more.. i dont know how to use macro give me some simple VBA code to workout myself. thanks..

hi alf, your code is working well. i tried your program its first time to me, nice thank you..

Glad to be of help and thanks for feed back.

Alf

Ps If this solves you problem don't forget to mark thread "Solved"

how to remove shortcut keys. i save in personal macro workbook, i want remove all shorcut keys. i kept shortcut for just i learning purpose..

THIS IS HAPPEN when i delete " cannot edit a macro on a hidden workbook. unhide the workbook using the unhide command"

Perhaps this link will help?

http://office.microsoft.com/en-us/he...#BMdeletemacro

Check part "Delete a macro"

Alf

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("B" & i)
        .Value = WorksheetFunction.Trim(.Value)
    End With
Next i
End Sub

this vba code for deleting B column empty or blank cell, i want like this to do more column delete blank cell, that columns are H, K, N, Q, S, Y
I dont know how to create vba, i know only macro recording..

hai, in my orginal excel i have formula's in H, K, N, Q, S and Y colomn, formula give result in some cells only. other cells appear blank, but truth is really all the cells is not blank. so i cant make pivot. in pivot table calculate blank cells as value. after that i delete blank cells in this H, K, N, Q, S, Y column then pivot table give correct value.

Not to happy about this macro still it works but it should be possible to write a more "condensed" macro,

Sub test2()
Dim LR As Long, i As Long, j As Long

Application.ScreenUpdating = False

With ActiveSheet.UsedRange
    i = .Rows(.Rows.Count).Row
End With

For j = 1 To i
    With Range("B" & j)
        .Value = WorksheetFunction.Trim(.Value)
    End With
Next

For j = 1 To i
    With Range("H" & j)
        .Value = WorksheetFunction.Trim(.Value)
    End With
Next

For j = 1 To i
    With Range("K" & j)
        .Value = WorksheetFunction.Trim(.Value)
    End With
Next

For j = 1 To i
    With Range("N" & j)
        .Value = WorksheetFunction.Trim(.Value)
    End With
Next

For j = 1 To i
    With Range("Q" & j)
        .Value = WorksheetFunction.Trim(.Value)
    End With
Next

For j = 1 To i
    With Range("S" & j)
        .Value = WorksheetFunction.Trim(.Value)
    End With
Next

For j = 1 To i
    With Range("Y" & j)
        .Value = WorksheetFunction.Trim(.Value)
    End With
Next

Application.ScreenUpdating = True

End Sub

Alf

thank you boss, i tried your vba, and learnt i want insert this vba in another one vba which one is my recorded macro vba, i will try to insert,

After fiddling around a bit I've managed to "compress" the previous macro. The string "sNam" contains the letters for the columns you wish to trim. Should you wish to trim other columns just change the letters in "sNam".

Option Explicit

Sub TrmTst()
Dim i As Long, j As Long, k As Long
Dim sNam As String, sL As String

With ActiveSheet.UsedRange
    i = .Rows(.Rows.Count).Row
End With

sNam = "BHKNQSY"

For j = 1 To Len(sNam)

sL = Mid(sNam, j, 1)

For k = 1 To i

 With Range(sL & k)
    .Value = WorksheetFunction.Trim(.Value)
End With

Next

Next

End Sub

Alf

Glad to be of help and thanks for your kind words!

As this problem now seems "Solved" could you pleas mark this thread "Solved"

Alf

Ps

To mark your thread solved do the following:
New Method
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved