excel vba End(xlDown) stopping on blank cell

[owentmoore]

Hi all

I have a module that searches column A for non blank cells and deletes the non blanks (it needs to do this one by one as it's part of a larger routine, selecting the entire column and deleting isn't an option).

The code is as follows:

Sub FindAndeDeleteNonBlanks()

' Find the first filled cell in column A1
Range("A1").End(xlDown).Select

' Loop finding and deleting fiiled cells until all non-blank cells deleted
Do Until ActiveCell.Value = ""

'Find the first used cell in a column
Range("A1").End(xlDown).Select
Selection.ClearContents
Range("A1").End(xlDown).Select

Loop

End Sub

The code works well for a number of deletions and then stops on a blank cell which it shouldn't do. In the example below it stops on cell A19 even though cell A21 below it contains a value. Another point, after I end the routine, if I manually select and clear the contents of the cells cleared during the routine. Then when I rerun the macro it finds and deletes the last value in cell A21!!

A1
A2
A3
A4
A5
A6
A7
A8 1.25
A9
A10 14.50
A11
A12
A13
A14 0.25
A15
A16 15.00
A17 13.50
A18 0.50
A19
A20
A21 0.83


I am totally at a loss as to why it isn't finding and deleting cell A21 when the macro is run first.

Please help.

Owen

 

[owentmoore]

I think now there is a problem or excel program error with the list of data I have.

When I run the code above it stops on A20 even though it appears blank. When I select cell A1 and then press Ctrl and Down Arrow cell A20 is selected telling me that excel thinks there is somthing in the cell even though there visually isn't.

As a test I've setup a formula in cell B1 as follows: =A1+1. I've copied this down and it adds 1 to the values and where there are no values in column A it also gives a resultant of 1 as you'd expect. However cells A20 and A21 both give me "#Value!" errors.

 

[Gavona]

Clearly A19 is not blank. Try =isblank(A19) to check this.

I can replicate your issue by putting ="" in A19. It looks blank but is not.

Instead of Do Until ActiveCell.Value = "" check for ActiveCell.row=65536

Personally, though I would do more like this (uses Edit,Goto,Special)

Sub Macro1()
'
Dim rng1 As Range
Dim rng2 As Range
Dim rngTarget As Range
Dim r As Range

Set rngTarget = Range("A:A")
Set rng1 = rngTarget.SpecialCells(xlCellTypeConstants)
Set rng2 = rngTarget.SpecialCells(xlCellTypeFormulas)
Set rngTarget = Union(rng1, rng2)

'Either
For Each r In rngTarget
    r.ClearContents
Next r

'OR
rngTarget.ClearContents

End Sub

Gavin

 

[owentmoore]

Thanks Gavin.

Your first suggestion works perfectly.

I found that my "non blank" blank cell originated from a formula I had that returned a blank "" into the cell if other conditions were not met. I then performed a paste special, pasting values only. I didn't know that the paste special would keep the "". I also didn't know that entering "" as a formula resultant puts a value into a cell (albeit "").

My formula is:
=IF(I137>=H138,"",((H138-I137)*24))

Is there a way to enter a blank in a field without the "" being entered into the cell?

Thanks
Owen

 

[SkipVought]

Hi. Try this...

Sub FindAndeDeleteNonBlanks()


' Loop finding and deleting fiiled cells until all non-blank cells deleted
Do Until Range("A1").End(xlDown).Row = Cells.Rows.Count
    
    Range("A1").End(xlDown).ClearContents

Loop

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Glad to have helped. I don't know of any way to make a formula return a truly blank cell when copied to values.

My second VBA solution, Skip's solution or even Range("A:A").ClearContents would appear to be the easiest EXCEPT that you said:

it needs to do this one by one as it's part of a larger routine, selecting the entire column and deleting isn't an option

My VBA code first identifies the cells that you want to clear. You then have the choice of:
a) Processing each individually "For each...."
or
b) Doing something to all of those cells. "rngTarget.ClearContents"

This second option could be built upon if you also want to do something to, say, the cells in column B for just those rows. In this case you might use rngTarget.offset(0,1).dosomething

Hope this helps,

Gavin