Empty Range

[kathy.aubin]

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]

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????

 

[Bernie Deitrick]

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]

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