Problem with Deleting Rows using SpecialCells

[dwelleronthethreshold]

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!

 

[headtoadie]

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]

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]

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.

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]

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]

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]

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]

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:-

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