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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

headtoadie

Board Regular
Joined
Aug 1, 2003
Messages
219
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
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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
 

dwelleronthethreshold

Board Regular
Joined
Jan 9, 2003
Messages
94
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
 

dwelleronthethreshold

Board Regular
Joined
Jan 9, 2003
Messages
94

ADVERTISEMENT

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.
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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
 

dwelleronthethreshold

Board Regular
Joined
Jan 9, 2003
Messages
94
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
 

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267
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
 

Forum statistics

Threads
1,147,507
Messages
5,741,566
Members
423,667
Latest member
Kai_357

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
Top