Deleting phantom rows or fields in Excel 2007

Hello,

I have about 3000+ rows of data with over 20 columns. Certain columns have blank fields but those blank fields seem like they contain phantom data or formats. Manually deleting those empty phantom rows by filtering blanks work, but I am wondering if there is a much simpler process to remove those phantom rows in those certain columns.
Jintonix415Asked:
Who is Participating?
 
kgerbChief EngineerCommented:
Brian,
I don't think you can just test for the cell being equal to "".  It will apply to every cell on the worksheet that doesn't contain something.  I may be wrong but I think you need to test for Not IsEmpty and the value equal to "".  Maybe I'm wrong but it seemed to work on the example workbook.

See attached workbook.
Sub ClearPhantomCells()
Dim rng As Range, rngSel As Range
For Each rng In Selection
    If Not IsEmpty(rng) And rng.Value = "" Then
        If rngSel Is Nothing Then Set rngSel = rng Else Set rngSel = Union(rngSel, rng)
    End If
Next rng
rngSel.Interior.ColorIndex = 3
'rngSel.ClearContents
End Sub

Open in new window

Q-27393303-RevB.xlsm
0
 
redmondbCommented:
Hi, Jintonix415,

Could you give us a redacted sample of these rows and let us know which of its cells are "phantom", please?

Thanks,
Brian.
0
 
kgerbChief EngineerCommented:
Hi Jintonix415,
This may be overly simplistic.  It selects all the blank cells in a given range and deletes them shifting up.  Adjust "MyRange" as necessary.
Sub DeleteBlanksShiftUp()
Range("MyRange").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End Sub

Open in new window

Let me know if I'm completely off base.

Kyle
Q-27393303-RevA.xlsm
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Jintonix415Author Commented:
Well heres the thing. Even if I send you a sample file I don't think there is any way to tell. I use an client called TDM that brings in the excel file and populates those rows to the application. The client could not load the excel file because of those phantom rows, which I manually deleted since it spotted which columns it was having issues with.
0
 
Jintonix415Author Commented:
kgerb, where do I apply that logic? Macro?
0
 
redmondbCommented:
Jintonix415,

And you don't have the pre-deleted version? If you do, if TDM sees a difference then we should be able to too. (Ah, the arrogance of ignorance!)

Regards,
Brian.
0
 
kgerbChief EngineerCommented:
Jintonix415,
Yes, it is a macro.  See the attached example workbook.

To access the VB editor press Alt+F11.  Press Ctrl+R until the project explorer opens.  You will find the macro in Module1.

The problem I see with my approach will be if the "phantom" data is not seen a a "blank" by excel.  Otherwise I think it will work fine.  Just change "MyRange" to your range of data containing the blanks and run the macro.  As always, when you are running a macro that deletes stuff be sure to make a copy first (macros are completely irreversible).  

Let me know if you need more info.

Kyle
0
 
Jintonix415Author Commented:
kgerb, I tried the macro and it looks like it did not work. This is the macro I used:

Sub DeleteBlanksShiftUp()
Range("$C$2:$AK$3295").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End Sub

redmondb:

Here is the original file but I deleted many rows and renamed the data since this file is a company private file. The columns are colored in red that contain the phantom data. Please take a look, thanks.




phantomrows.xlsx
0
 
redmondbCommented:
Jintonix415,

OK, I can see the baddies. The code below puts a null formula in all blank cells (=""). If the export/import to TDM doesn't mind formulas, then this might be OK. Please give it a go.
Sub De_Phantom()
Dim xcell As Range
Dim xLast_Row As Long
Dim xLast_Col As Long

Sheets("Sheet1").Activate

xLast_Row = Range("A1").SpecialCells(xlLastCell).Row
xLast_Col = Range("A1").SpecialCells(xlLastCell).Column

For Each xcell In Range(Cells(1, 1), Cells(xLast_Row, xLast_Col))
    If xcell.Value = "" Then
        xcell.Value = "="""""
'        xcell.Formula = xcell.Value
    End If
Next

End Sub

Open in new window

Regards,
Brian.
0
 
redmondbCommented:
Jintonix415,

Please see attached. There are two sheets - I reckon one will fail every blank, whereas the other should pass. Fingers crossed!

Regards,
Brian phantomrows-V2.xlsm
0
 
redmondbCommented:
Sorry, crossing posts. I'll have a look at yours, but please check out mine!

Thanks,
Brian.
0
 
redmondbCommented:
Yes, I reckon "Attempt 2" is the same as yours.

Oops, just tried your code against the original file and it didn't "convert" it - just changed colour. I then ran my "Atttempt 2" code and it seemed to work.

Regards,
Brian.
0
 
Jintonix415Author Commented:
redmondb,

I tried your first macro and unfortunately it does not work due to the formula. I also did not see your attempt 2 code.

Kgerb,

I received a runtime error with your code.

'Object variable or With block variable not set.
0
 
redmondbCommented:
No, don't bother with the code - just try the sheets!
0
 
Jintonix415Author Commented:
Sorry I dont follow. Do you want me to try importing the data to the sheet or load the attempt 2 sheet into my client application?
0
 
redmondbCommented:
Sorry - load the attempt 2 sheet into the client application!
0
 
Jintonix415Author Commented:
Yes that worked.
0
 
redmondbCommented:
Great! Ok, could you now run your code against the first file you posted - download the file from here, to be sure, to be sure - and try to load that.
0
 
Jintonix415Author Commented:
It did not work as it still shows the forumulas which the client does not like. I used your initial code you posted again the file I posted.
0
 
redmondbCommented:
Jintonix415,

No, I wanted you to run your code. My "Attempt 2" sheet is the same as the last file you posted. However, when I ran your code against your original file it didn't give me the same result as your last file. So, I wondered had you inadvertently done something else to that file, hence my request for you to re0run your code against the original above.

That OK? In the meantime, I'll post the "Attempt 2" code. (In a few minutes - I'm doing some juggling at the moment.)

Regards,
Brian.
0
 
Jintonix415Author Commented:
I did not run any code on my own file, are you talking about Kgerb's code?? I think thats where the confusion lies.
0
 
redmondbCommented:
Jintonix415,

Aha, all is now clear! The code you sent me is not what you ran to fix the file - it has the ClearContents commented out and ClearContents is the magic bullet (that's what I used for "Attempt 2" also.)

So, I would suggest you close the question allocating all the points to your good self!

Regards,
Brian.

0
 
Jintonix415Author Commented:
I am bit confused. Is this the code you are talking about?

Sub ClearPhantomCells()
Dim rng As Range, rngSel As Range
For Each rng In Selection
    If Not IsEmpty(rng) And rng.Value = "" Then
        If rngSel Is Nothing Then Set rngSel = rng Else Set rngSel = Union(rngSel, rng)
    End If
Next rng
rngSel.Interior.ColorIndex = 3
rngSel.ClearContents
End Sub

Open in new window


If so I am getting error when attempting to run it.

'Object variable or With block variable not set.

0
 
redmondbCommented:
Bet you didn't remember to select all the cells first!
0
 
Jintonix415Author Commented:
Yep, didn't know I had to select the cells, but how did you get rid of the color?

0
 
redmondbCommented:
Simply comment out the line...
rngSel.Interior.ColorIndex = 3

I thought this was your code. Where did it come from?
0
 
Jintonix415Author Commented:
Great it worked!!! Geniuses!! The code was brought to by kberg. But you have provided major assistance. So thank you very much you two!!!!
0
 
redmondbCommented:
Where was it? It arrived before my "Attempt 2" (which you never even saw the code for).He deserves all the points.
0
 
redmondbCommented:
Oops, I clean missed that the "RevB" was his not yours. To compound things, the version he posted had the comments on the last two lines reversed!

Definitely kgerb's points.
0
 
redmondbCommented:
Too generous, but thanks anyway!
0
 
kgerbChief EngineerCommented:
wow, sorry I missed all the discussion.  Had to leave work early last night.

Jintonix415,
Thank you for splitting with Brian.  Without his input my help would not have worked.  

Brian,
Thank you for helping implement my code in my absence.

Kyle
0
 
redmondbCommented:
Thanks, Kyle - not my finest hour!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.