Logic of xlCellTypeBlanks i.e. "Blanks" period or "Blanks" in used range?

[Dennis]

Excel 2003

In the following VBA code, I am attempting to:

Delete Rows where there are blanks in column H

The code below SEEMS to work where there are NO blanks in Col H.

But when there ARE blanks in Col H, I still get an Error via MsgBox. Therefore, an Error occurred
triggering the MsgBox display. As a result the Row with a blank in Column H was not deleted.


Columns("A:W").AutoFilter Field:=8, Criteria1:="="
On Error Resume Next ' In case there no blanks
Selection = Range("H2", Selection.SpecialCells(xlCellTypeBlanks)).Select
If Err <> 0 Then
MsgBox "NOTE: Empty Rows were not located!"
Else
Selection.EntireRow.Delete
End If
On Error GoTo 0


Why does an error occur if, in fact, there is a blank in H?

Bottom line, I would like to be notified when there are no blank cells in Col H and have no rows
deleted. That said, if there are blanks in Column H, I do want those Rows deleted. I can not seem
to get both concepts to work correctly. Obviously, my logic is flawed.

Is it correct to assume that xlCellTypeBlanks are blanks in the Used Range?

TIA Dennis

 

[Ron de Bruin]

Hi

Try this example
Change the range to yours

Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range

DeleteValue = ""

With ActiveSheet
.Range("H2:H" & .Cells(.Rows.Count, "H").End(xlUp).Row) _
.AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
If rng Is Nothing Then MsgBox "No empty cells"
End With
.AutoFilterMode = False
End With
End Sub

 

[Dennis]

Ron,

Thanks for your help.

Did you mean use:

rng = Range("H2", Selection.SpecialCells(xlCellTypeBlanks))
-OR-
rng = Range("H2", Selection.SpecialCells(xlCellTypeLastCell))
-OR-
rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)

Again thanks!

Dennis

 

[Ron de Bruin]

..Range("H2:H" & .Cells(.Rows.Count, "H").End(xlUp).Row)

My Macro start in H2 till the last row with data in Column H
H2 is the cell with the Header

If this is your range then you don't have to change anything
Try the macro

 

[Dennis]

Ron,

Your approach works fine!

I just wish I knew where my error was.

Thanks Dennis

 

[Dave Peterson]

This line looks pretty weird to me:

Selection = Range("H2", Selection.SpecialCells(xlCellTypeBlanks)).Select

And using Selection in your code makes it dependent on what was, er, selected.

And I bet you want to limit those specialcells to just column H.

range("H:H").cells.specialcells(xlcelltypeblanks).select

But I wouldn't use the selection at all.

Ron's code uses the autofilter range (not the whole column) and he avoids the
first row--just in case that was empty.

And since he filtered by column H, he can use any column and the visible cells
in that column (Ron used the first column in the autofilter range.)

 

[Dennis]

Thanks for your thoughts Dave.

I found very little in the way of documentation re: xlCellTypeBlanks vs Visible.

In fact, when I selected "visible" cells in the Immediate Window I saw the contents of the window move but could not find any cell selected.

Guess, I just do and accept.

One day ........

 

[Dave Peterson]

You want to see how those specialcells work?

The easiest way is to create some test data in a worksheet.
Then select your range
Edit|goto|special|blanks (or visible or whatever)

You'll see what's happening pretty quickly.

(Remember to hide some rows/columns when you're doing this.)

And if you copy those cells and paste them to a different sheet, that may help,
too.

Blanks = cells that have nothing in them (no formula, no value)

Visible = the ones you can see when you're scrolling around--not hidden by
hiding the row/column or even by a filter.

 

[Dennis]

Great pointers Dave!

You want to see how those specialcells work?

The easiest way is to create some test data in a worksheet.
Then select your range
Edit|goto|special|blanks (or visible or whatever)

You'll see what's happening pretty quickly.

(Remember to hide some rows/columns when you're doing this.)

And if you copy those cells and paste them to a different sheet, that may help,
too.

Blanks = cells that have nothing in them (no formula, no value)

Visible = the ones you can see when you're scrolling around--not hidden by
hiding the row/column or even by a filter.