Deleting phantom rows or fields in Excel 2007

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.

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

Select allOpen in new window

Let me know if I'm completely off base.

Kyle
Q-27393303-RevA.xlsm

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.

kgerb, where do I apply that logic? Macro?

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.

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

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

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

Select allOpen in new window

Regards,
Brian.

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

Sorry, crossing posts. I'll have a look at yours, but please check out mine!

Thanks,
Brian.

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.

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.

No, don't bother with the code - just try the sheets!

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?

Sorry - load the attempt 2 sheet into the client application!

Yes that worked.

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.

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.

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.

I did not run any code on my own file, are you talking about Kgerb's code?? I think thats where the confusion lies.

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.

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

Select allOpen in new window

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

'Object variable or With block variable not set.

Bet you didn't remember to select all the cells first!

Yep, didn't know I had to select the cells, but how did you get rid of the color?

Great it worked!!! Geniuses!! The code was brought to by kberg. But you have provided major assistance. So thank you very much you two!!!!

Where was it? It arrived before my "Attempt 2" (which you never even saw the code for).He deserves all the points.

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.

Too generous, but thanks anyway!

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

Thanks, Kyle - not my finest hour!