[SOLVED] How do I identify blank cells?

[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.

[Norman Jones]

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.

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

[Norman Jones]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>>
>>
>
>

[peanburn via OfficeKB.com]

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

[Norman Jones]

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

[peanburn via 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