Thread: removing blank cells

I am not sure if this is possible to do easily but i am trying to remove all of the blanks in a document. if you look at the attached, you can see what i mean. In sheet 1 i have the messy data and sheet 2 i have what i would like the sheet to look like in the end. while this may seem easy to do using an advanced filter, the actual data i have is 150 rows by 150 columns with data spread throughout. Thanks

try......

Code:

Sub removingblank()

    Dim intCol As Integer
     
    For intCol = 1 To 5
        Range(Cells(1, intCol), Cells(353, intCol)). _
        SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
    Next intCol
End Sub

thanks. It didnt work for me when i tried to use it

it worked for the example i gave but when i tried to apply it to a bigger set of numbers, it keeps spitting back an error

here's your file with the macro posted by johncena installed.
I added a line to copy your data from sheet1 to sheet2 and then performing the blank cell deletion on sheet2.
This way your data on sheet1 remains in tact.
hope this helps.
you can remove the button I added and execute the macro manually.
modytrane.

this all works great for the attachment that i gave but the data that i am using is actually in a grid that is 154 columnsx155 rows and for some reason the macro is not working in the larger data set. is there something that i need to alter in the macro to make it work. i see tha tit says "For intCol - 1 To 5" but even if i change that to 1 To 154 it still has the following error "Run-time error '1004': Application-defined or object -defined error"

Thank you all for your help. its been great

OK, it should work now.
In the attached file, I've added dummy data all the way to column FZ [which should be 182nd column], down to ROW 180+.
The macro has a shortcut of ctrl+d.
I've cleared sheet 2, so its blank.
Run the macro and data will be copied to sheet2 and then compressed up.
modytrane.

do you happen to have the VBA code for that so i can run it in my other file? Thanks

Here's the code.

Code:

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

For future reference, you can open the file and enter alt+F11 and view the code in module(s).

Good Luck.
If you have problems, post back.

modytrane

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

I looked at your file last night and the problem is that those "blank" cells are not really blank. I don't know if they are results of some formulas or you are downloading the data from another source. Anyway, the macro instruction I used [originally posted by johncena] is looking for "blank" cells.

When I get a chance later, I'll modify it to look for non-zero values and delete the rest.

modytrane.

those cells were a result of a formula and they are paste special>values. sorry about that. i thought that would make them blank

kmknowles,
Here's a solution that will work in your situation.
See the attached example. I modified the original code from johncena's post.
Now isteadof special cells being blank, we are looking for non-zero length text.

Here's the macro.

Code:

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)).Select
        Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Delete Shift:=xlUp
       
    Next intCol
End Sub

Try it, it works.

modytrane.

that worked great on your attachement but i am getting the same error when i run it on my attachement from above

When i run the first macro from JohnCena, I am getting a "Compile error: Wrong number of arguments or invalid property assignment"

I dont know if i am doing it wrong but what i am doing is going into sheet 1 and right clicking the tab and then viewing code and copy and pasting it there. then when i click F5 i get the error.

Thank you all for your help on this. I am trying to streamline a process that i use often and this would really help me out.