How do I identify blank cells?

  • Thread starter Thread starter peanburn
  • Start date Start date
P

peanburn

Just learning here. I am trying to identify blank cells and to remove the
rows containing them to another sheet. I have played around with the
IgnoreBlank property but am getting nowhere.

With Range(Cells(2, 3), Cells(1500,3)).Validation
.Delete
.IgnoreBlank = False
End With

Any help would be appreciated. Thanks.
 
Hi Peanburn,

As an example of selecting all rows on the active sheet which are blank in
column A, copying these rows to another sheet and then deleting the original
blank rows, try something like:

'=============>>
Public Sub Tester004()
Dim rng1 As range
Dim rng2 As range

Set rng2 = Sheets("Sheet2").range("A1")

On Error Resume Next
Set rng1 = range("A:A"). _
SpecialCells(xlCellTypeBlanks).EntireRow
On Error GoTo 0

If Not rng1 Is Nothing Then
With rng1
.Copy Destination:=rng2
.Delete
End With
End If
End Sub
'<<=============
 
Ok, this might sound like a dumb question.......

Why would one copy blank rows to another location?

If they're blank, there's nothing to copy.

Biff
 
Hi Biff,

The rows are defined as blank depending on the column A value - other cells
in such rows are not constrained to be empty.
 
Works great! Thanks a lot.
I understand the "On Error" sequence, but am having trouble with

If Not rng1 Is Nothing Then
With rng1
.Copy Destination:=rng2
.Delete
End With
End If
Click to expand...

I understand the "with" statements. What is the "If Not rng1 Is Nothing
Then" statement doing?
 
Hi Peanburn,
I understand the "On Error" sequence, but am having trouble with

If Not rng1 Is Nothing Then
Click to expand...

I would have liked to say

If rng1 Is Something Then (i.e. if there is something to copy)

but the keyword Something does not exist. Since, however, the keyword
Nothing does exist, I use this with a double negative to obtain a valid
equivalent expression.
 
Back
Top