Removing blanks from a spreadsheet

[Guest]

I created a huge data analysis file that consists of 50 by 214 cells.
According to my formula's, it will only show data if a lot of demands are
met. In reality, this means, about 15 to 20 of them have data, and the others
are blank.

I now want to quickly remove all the blanks. However, Go To>Special>blanks,
doesn't work, because for some reason, Excel doesn't consider my blanks to be
blank, even after I copied the values to another worksheet, and am certain
that these cells are actually blank and empty.

Soooo, do any of you experts know a solution for this problem? Or another
way to quickly gather these appx. 20 cells that do have data in them in one
column? Without blanks? I'd hate to do that manually.

Thank you in advance. I'll be in bed really soon, cause it is terribly late
in Europe, but I'll be back first thing in the morning to check your ideas,
and provide feedback or add info I forgot to post right away...

 

[Gazeta]

I created a huge data analysis file that consists of 50 by 214 cells.
According to my formula's, it will only show data if a lot of demands are
met. In reality, this means, about 15 to 20 of them have data, and the others
are blank.

I now want to quickly remove all the blanks. However, Go To>Special>blanks,
doesn't work, because for some reason, Excel doesn't consider my blanks to be
blank, even after I copied the values to another worksheet, and am certain
that these cells are actually blank and empty.

Soooo, do any of you experts know a solution for this problem? Or another
way to quickly gather these appx. 20 cells that do have data in them in one
column? Without blanks? I'd hate to do that manually.

Thank you in advance. I'll be in bed really soon, cause it is terribly late
in Europe, but I'll be back first thing in the morning to check your ideas,
and provide feedback or add info I forgot to post right away...

try
Sub test()
Dim act As Worksheet

Set act = ActiveSheet
Sheets.Add After:=Sheets(act.Index)
ActiveSheet.Name = "formulas"
Row = 2
For Each cell In act.Range("a1").SpecialCells(xlCellTypeFormulas, 23)
If cell.Value <> "" Then
cell.Copy
Sheets("formulas").Range("a" & Row).PasteSpecial xlPasteValues
Row = Row + 1
End If
Next
End Sub

run it when your sheet is active
mcg

 

[Guest]

Brilliant, thank you very much.

I could have never thought of the line "For each cell etc..."

Niels