Empty Range

Hi,

I'm trying to do a function who delete all the row of a range I pass is
argument and will copy them to an other sheet. Here is my function :

Function RangeToDelete(ToBeDelete As Range, SheetsNameCopy As String)
If WorksheetFunction.CountBlank(ToBeDelete) = 0 Then
Selection.EntireRow.Copy
Sheets(SheetsNameCopy).Paste
Selection.EntireRow.Delete
End If
End Function

I want to check if the range is empty first so I don't have an error
cause I can't delete something that is empty. But this function doesn't
work since the CountBlank is not working. I have an error telling me
that it's not available.

Can you help me on this?!

Thanks

Your code worked ok for me.

But are you sure you want to use =countblank()? If you have a cell in that
tobedelete range that's empty (or evaluates to ""), then that one cell will make
the =countblank() bigger than 0.

Maybe

if application.counta(tobedelete) > 0 then
'do the work
end if

So you're just checking to see if there's any cell in that range that is
non-empty.

ps.

Is there a reason you used Selection.entirerow.copy. I would have guessed that
you would have used the tobedelete range that you passed to the function. (I
don't see why your function would care about the selected range????



kathy.aubin@gmail.com wrote:
>
> Hi,
>
> I'm trying to do a function who delete all the row of a range I pass is
> argument and will copy them to an other sheet. Here is my function :
>
> Function RangeToDelete(ToBeDelete As Range, SheetsNameCopy As String)
> If WorksheetFunction.CountBlank(ToBeDelete) = 0 Then
> Selection.EntireRow.Copy
> Sheets(SheetsNameCopy).Paste
> Selection.EntireRow.Delete
> End If
> End Function
>
> I want to check if the range is empty first so I don't have an error
> cause I can't delete something that is empty. But this function doesn't
> work since the CountBlank is not working. I have an error telling me
> that it's not available.
>
> Can you help me on this?!
>
> Thanks

--

Dave Peterson

Kathy,

You would do it like so, using a Sub, not a function (functions shouldn't be used to manipulate
ranges):

Sub RangeToDelete(ToBeDelete As Range, SheetsNameCopy As String)
If Application.WorksheetFunction.CountBlank(ToBeDelete) = 0 Then
ToBeDelete.EntireRow.Copy _
Sheets(SheetsNameCopy).Cells(Rows.Count, 1).End(xlUp)(2)
ToBeDelete.EntireRow.Delete
End If
End Sub

Sub Test()
RangeToDelete Range("A1:A3"), "Sheet2"
End Sub

But, it will work without the check:

Sub RangeToDelete(ToBeDelete As Range, SheetsNameCopy As String)
ToBeDelete.EntireRow.Copy _
Sheets(SheetsNameCopy).Cells(Rows.Count, 1).End(xlUp)(2)
ToBeDelete.EntireRow.Delete
End Sub

Sub Test()
RangeToDelete Range("A1:A3"), "Sheet2"
End Sub

HTH,
Bernie
MS Excel MVP


<kathy.aubin@gmail.com> wrote in message
news:1130344589.513206.111190@f14g2000cwb.googlegroups.com...
> Hi,
>
> I'm trying to do a function who delete all the row of a range I pass is
> argument and will copy them to an other sheet. Here is my function :
>
> Function RangeToDelete(ToBeDelete As Range, SheetsNameCopy As String)
> If WorksheetFunction.CountBlank(ToBeDelete) = 0 Then
> Selection.EntireRow.Copy
> Sheets(SheetsNameCopy).Paste
> Selection.EntireRow.Delete
> End If
> End Function
>
> I want to check if the range is empty first so I don't have an error
> cause I can't delete something that is empty. But this function doesn't
> work since the CountBlank is not working. I have an error telling me
> that it's not available.
>
> Can you help me on this?!
>
> Thanks
>

Thank you both of you! I've used a mix of your two idea! And I don't
why the CountBlank function is not working but the CountA is working
properly!

Thanks again,

Kathy