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
'<<=============


---
Regards,
Norman



"peanburn" <u18802@uwe wrote in message news:5bff4170941c8@uwe...
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.

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

"Norman Jones" wrote in message
...
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
'<<=============


---
Regards,
Norman



"peanburn" <u18802@uwe wrote in message news:5bff4170941c8@uwe...
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 Biff,

The rows are defined as blank depending on the column A value - other cells
in such rows are not constrained to be empty.


---
Regards,
Norman



"Biff" wrote in message
...
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

"Norman Jones" wrote in message
...
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
'<<=============


---
Regards,
Norman



"peanburn" <u18802@uwe wrote in message news:5bff4170941c8@uwe...
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.

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

I understand the "with" statements. What is the "If Not rng1 Is Nothing
Then" statement doing?

Norman Jones wrote:
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:


--
Message posted via http://www.officekb.com

Hi Peanburn,

I understand the "On Error" sequence, but am having trouble with

If Not rng1 Is Nothing Then

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.


---
Regards,
Norman



"peanburn via OfficeKB.com" <u18802@uwe wrote in message
news:5c06fe73933b2@uwe...
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

I understand the "with" statements. What is the "If Not rng1 Is Nothing
Then" statement doing?

Norman Jones wrote:
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:


--
Message posted via http://www.officekb.com

Thanks. It makes sense. I really appreciate the help.


Norman Jones wrote:
Hi Peanburn,

I understand the "On Error" sequence, but am having trouble with

If Not rng1 Is Nothing Then

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.

---
Regards,
Norman


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200602/1