Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Deleting phantom rows or fields in Excel 2007

Posted on 2011-10-12
32
Medium Priority
?
1,090 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:Jintonix415
  • 17
  • 11
  • 4
32 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 36957385
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
 
LVL 12

Expert Comment

by:kgerb
ID: 36957412
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
 

Author Comment

by:Jintonix415
ID: 36957434
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Jintonix415
ID: 36957443
kgerb, where do I apply that logic? Macro?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36957456
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
 
LVL 12

Expert Comment

by:kgerb
ID: 36957504
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
 

Author Comment

by:Jintonix415
ID: 36957605
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
 
LVL 26

Expert Comment

by:redmondb
ID: 36957900
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
 
LVL 12

Accepted Solution

by:
kgerb earned 1400 total points
ID: 36958084
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
 
LVL 26

Expert Comment

by:redmondb
ID: 36958102
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
 
LVL 26

Expert Comment

by:redmondb
ID: 36958110
Sorry, crossing posts. I'll have a look at yours, but please check out mine!

Thanks,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36958155
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
 

Author Comment

by:Jintonix415
ID: 36958212
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
 
LVL 26

Expert Comment

by:redmondb
ID: 36958218
No, don't bother with the code - just try the sheets!
0
 

Author Comment

by:Jintonix415
ID: 36958230
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
 
LVL 26

Expert Comment

by:redmondb
ID: 36958260
Sorry - load the attempt 2 sheet into the client application!
0
 

Author Comment

by:Jintonix415
ID: 36958312
Yes that worked.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36958343
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
 

Author Comment

by:Jintonix415
ID: 36958426
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
 
LVL 26

Expert Comment

by:redmondb
ID: 36958494
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
 

Author Comment

by:Jintonix415
ID: 36958537
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
 
LVL 26

Expert Comment

by:redmondb
ID: 36958550
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
 

Author Comment

by:Jintonix415
ID: 36959003
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
 
LVL 26

Expert Comment

by:redmondb
ID: 36959049
Bet you didn't remember to select all the cells first!
0
 

Author Comment

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

0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 600 total points
ID: 36959087
Simply comment out the line...
rngSel.Interior.ColorIndex = 3

I thought this was your code. Where did it come from?
0
 

Author Comment

by:Jintonix415
ID: 36959176
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
 
LVL 26

Expert Comment

by:redmondb
ID: 36959198
Where was it? It arrived before my "Attempt 2" (which you never even saw the code for).He deserves all the points.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36959203
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
 
LVL 26

Expert Comment

by:redmondb
ID: 36959266
Too generous, but thanks anyway!
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36961623
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
 
LVL 26

Expert Comment

by:redmondb
ID: 36963784
Thanks, Kyle - not my finest hour!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question