software

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:

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

Set rng2 = Sheets(quot;Sheet2quot;).range(quot;A1quot;)

On Error Resume Next
Set rng1 = range(quot;A:Aquot;). _
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
'lt;lt;=============---
Regards,
Norman
quot;peanburnquot; lt;u18802@uwegt; wrote in message news:5bff4170941c8@uwe...
gt; Just learning here. I am trying to identify blank cells and to remove the
gt; rows containing them to another sheet. I have played around with the
gt; IgnoreBlank property but am getting nowhere.
gt;
gt; With Range(Cells(2, 3), Cells(1500,3)).Validation
gt; .Delete
gt; .IgnoreBlank = False
gt; End With
gt;
gt; 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

quot;Norman Jonesquot; gt; wrote in message
...
gt; Hi Peanburn,
gt;
gt; As an example of selecting all rows on the active sheet which are blank in
gt; column A, copying these rows to another sheet and then deleting the
gt; original blank rows, try something like:
gt;
gt; '=============gt;gt;
gt; Public Sub Tester004()
gt; Dim rng1 As range
gt; Dim rng2 As range
gt;
gt; Set rng2 = Sheets(quot;Sheet2quot;).range(quot;A1quot;)
gt;
gt; On Error Resume Next
gt; Set rng1 = range(quot;A:Aquot;). _
gt; SpecialCells(xlCellTypeBlanks).EntireRow
gt; On Error GoTo 0
gt;
gt; If Not rng1 Is Nothing Then
gt; With rng1
gt; .Copy Destination:=rng2
gt; .Delete
gt; End With
gt; End If
gt; End Sub
gt; 'lt;lt;=============
gt;
gt;
gt; ---
gt; Regards,
gt; Norman
gt;
gt;
gt;
gt; quot;peanburnquot; lt;u18802@uwegt; wrote in message news:5bff4170941c8@uwe...
gt;gt; Just learning here. I am trying to identify blank cells and to remove
gt;gt; the
gt;gt; rows containing them to another sheet. I have played around with the
gt;gt; IgnoreBlank property but am getting nowhere.
gt;gt;
gt;gt; With Range(Cells(2, 3), Cells(1500,3)).Validation
gt;gt; .Delete
gt;gt; .IgnoreBlank = False
gt;gt; End With
gt;gt;
gt;gt; Any help would be appreciated. Thanks.
gt;
gt;
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
quot;Biffquot; gt; wrote in message
...
gt; Ok, this might sound like a dumb question.......
gt;
gt; Why would one copy blank rows to another location?
gt;
gt; If they're blank, there's nothing to copy.
gt;
gt; Biff
gt;
gt; quot;Norman Jonesquot; gt; wrote in message
gt; ...
gt;gt; Hi Peanburn,
gt;gt;
gt;gt; As an example of selecting all rows on the active sheet which are blank
gt;gt; in column A, copying these rows to another sheet and then deleting the
gt;gt; original blank rows, try something like:
gt;gt;
gt;gt; '=============gt;gt;
gt;gt; Public Sub Tester004()
gt;gt; Dim rng1 As range
gt;gt; Dim rng2 As range
gt;gt;
gt;gt; Set rng2 = Sheets(quot;Sheet2quot;).range(quot;A1quot;)
gt;gt;
gt;gt; On Error Resume Next
gt;gt; Set rng1 = range(quot;A:Aquot;). _
gt;gt; SpecialCells(xlCellTypeBlanks).EntireRow
gt;gt; On Error GoTo 0
gt;gt;
gt;gt; If Not rng1 Is Nothing Then
gt;gt; With rng1
gt;gt; .Copy Destination:=rng2
gt;gt; .Delete
gt;gt; End With
gt;gt; End If
gt;gt; End Sub
gt;gt; 'lt;lt;=============
gt;gt;
gt;gt;
gt;gt; ---
gt;gt; Regards,
gt;gt; Norman
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;peanburnquot; lt;u18802@uwegt; wrote in message news:5bff4170941c8@uwe...
gt;gt;gt; Just learning here. I am trying to identify blank cells and to remove
gt;gt;gt; the
gt;gt;gt; rows containing them to another sheet. I have played around with the
gt;gt;gt; IgnoreBlank property but am getting nowhere.
gt;gt;gt;
gt;gt;gt; With Range(Cells(2, 3), Cells(1500,3)).Validation
gt;gt;gt; .Delete
gt;gt;gt; .IgnoreBlank = False
gt;gt;gt; End With
gt;gt;gt;
gt;gt;gt; Any help would be appreciated. Thanks.
gt;gt;
gt;gt;
gt;
gt;
Works great! Thanks a lot.
I understand the quot;On Errorquot; sequence, but am having trouble with

If Not rng1 Is Nothing Then
gt; With rng1
gt; .Copy Destination:=rng2
gt; .Delete
gt; End With
gt; End If

I understand the quot;withquot; statements. What is the quot;If Not rng1 Is Nothing
Thenquot; statement doing?

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

--
Message posted via www.officekb.com

Hi Peanburn,

gt; I understand the quot;On Errorquot; sequence, but am having trouble with
gt;
gt; 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
quot;peanburn via OfficeKB.comquot; lt;u18802@uwegt; wrote in message
news:5c06fe73933b2@uwe...
gt; Works great! Thanks a lot.
gt; I understand the quot;On Errorquot; sequence, but am having trouble with
gt;
gt; If Not rng1 Is Nothing Then
gt;gt; With rng1
gt;gt; .Copy Destination:=rng2
gt;gt; .Delete
gt;gt; End With
gt;gt; End If
gt;
gt; I understand the quot;withquot; statements. What is the quot;If Not rng1 Is Nothing
gt; Thenquot; statement doing?
gt;
gt; Norman Jones wrote:
gt;gt;Hi Peanburn,
gt;gt;
gt;gt;As an example of selecting all rows on the active sheet which are blank in
gt;gt;column A, copying these rows to another sheet and then deleting the
gt;gt;original
gt;gt;blank rows, try something like:
gt;gt;
gt;
gt; --
gt; Message posted via www.officekb.com
Thanks. It makes sense. I really appreciate the help.Norman Jones wrote:
gt;Hi Peanburn,
gt;
gt;gt; I understand the quot;On Errorquot; sequence, but am having trouble with
gt;gt;
gt;gt; If Not rng1 Is Nothing Then
gt;
gt;I would have liked to say
gt;
gt; If rng1 Is Something Then (i.e. if there is something to copy)
gt;
gt;but the keyword Something does not exist. Since, however, the keyword
gt;Nothing does exist, I use this with a double negative to obtain a valid
gt;equivalent expression.
gt;
gt;---
gt;Regards,
gt;Norman
gt;

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