New Contributor

I'd like creat a vba code that copy only cells that not contais 0 to it a did that

l = 0
l_end = Range("G1").End(xlDown).Row

While l < l_end

    Do Until Range("I" & l_end).Value <> "0"
        l_end = l_end - 1
    If Range("I" & l_end).Value <> "0" Then
        Range("A1:V" & l_end).Copy
        linha = l_end + l
    End If
linha = l + 1

howeverIf my Table only have 0 I want to show a message but while excute this part of code l_end will return ranger "0" and debug o code, I would like show a message insteded that "The form is blank" .

Do Until Range("I" & l_end).Value <> "0"
l_end = l_end - 1

How can I do this?


2 Replies


Before you start your loop try to do some calculations to check that interval has values <> of Zeros

Something like

If application.WorksheetFunction.Sum(Your_Range) = 0 then

Msgbox ("Your Custom message") 

Exit sub


[Your Loop]

End if

@Juliano-Petrukio Tanks for answer But I need to know the number of last line completed To select and copy.