Problem with Deleting Rows using SpecialCells

dwelleronthethreshold

Board Regular
Joined
Jan 9, 2003
Messages
94
Hi all!

First, i'm still a relative "newbie" when it comes to using VBA, but i'm usually able to cobble something together through a combination of recording keystrokes/mouseclicks and pirating code found here and elsewhere. Anyway, my problem:

I was asked to take an AP and AR aging report extracted through Oracle and "make it usuable." The hard stuff is done, but I'm having trouble with the final clean up. In order to accomplish my objective, I had to insert a couple of columns (A, B, & C) and write some formulas. The formula in Col A is an If/And statement that will either return the customer/supplier name or "". I copy the formulas down and then paste values over the top. After that I would like to delete all rows in which the cell in col A is blank using the following:

Range("A1:A" & LastRow).Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Apparently, though, the cells in which the formula should have returned a "", are not in fact blank. Nothing shows in the formula bar, but the above routine doesn't recognize them as "blank." If I highlight the range of former "", and hit the delete key, the above code will do its job. Anyone have any ideas on how to get around this? I have another routine that wil loop through and delete rows based on the word False (which the formula could be modified to produce), but that method is much slower, perticularly with 33,000 rows. Given my VBA skills, or lack thereof, I'm open to all suggestions.

TIA!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Usually what I do in situations like this is put in a unique sequence instead of double quotes. Something like +++, then I can sort the data and easily remove the lines. Try to pick a sequence that will always sort to the bottom or top of your data, it's easier to remove that way.

If you are worried about the order of the data getting messed up insert a new column in A and fill in sequential numbers all the way down, you can then resort by this column after the +++ sort to rearrange the data back to original format.

HT
 
Upvote 0
Dweller,

The XlcellTypeBlanks will only find blanks(ie. No Formula's or characters)
You will need to do this.

Dim cell as range

for each cell in range("A2",range("A65535").end(xlup))
if cell.value ="" then cell.rows.entirerow.delete
next

HTH
Cal
 
Upvote 0
I gave that a shot, but it didn't work. My routine currently pastes values over the formulas. Should I keep the formulas "live" and then paste over them after your snippet? UPDATE: Actually I can't do that (paste values after the delete routine) as the formulas will not work properly.

Cbrine said:
Dweller,

The XlcellTypeBlanks will only find blanks(ie. No Formula's or characters)
You will need to do this.

Dim cell as range

for each cell in range("A2",range("A65535").end(xlup))
if cell.value ="" then cell.rows.entirerow.delete
next

HTH
Cal
 
Upvote 0
headtoadie said:
Usually what I do in situations like this is put in a unique sequence instead of double quotes. Something like +++, then I can sort the data and easily remove the lines.

Thanks for the response. Actually I can do that already with the "" in the if statement as Col A will be either blank or a name. I just wanted to avoid any manual processes as the CFO will be using this regularly.
 
Upvote 0
Dweller,
Can you put a break point on the code at the If cell.value ="" line.
Execute the code and type

? cell.value
in the immediate window. Once it shows a blank for the cell.value step through the code and make sure if executes the row.entire.delete.
The only way my code wouldn't work is when the value in column A is not equal to "", or if it's not column A.

Cal
 
Upvote 0
Thanks all for the responses. I ended up using a "x" rather than a "" in my IF statement, and then using the Replace function through VBA to replace the x's with nothing. After that, the SpecialCells Blank piece worked just fine.

Again, thanks for the help!

ER
 
Upvote 0
Dave McRitchies Trimall macro works wonders in cases like this, and is probably one of the most used pieces of code I have in my personal.xls

It will clean up almost anything, whether it be data from Access, the web, other sources etc:-

Code:
Sub a_TrimALL()
   'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
      Dim Cell As Range
      'Also Treat CHR 0160, as a space (CHR 032)
       Selection.Replace what:=Chr(160), Replacement:=Chr(32), _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
      'Trim in Excel removes extra internal spaces, VBA does not
   On Error Resume Next   'in case no text cells in selection
      For Each Cell In Intersect(Selection, _
         Selection.SpecialCells(xlConstants, xlTextValues))
         Cell.Value = Application.Trim(Cell.Value)
      Next Cell
   On Error GoTo 0
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top