ABOUT vba

%3CLINGO-SUB%20id%3D%22lingo-sub-1612459%22%20slang%3D%22en-US%22%3EABOUT%20vba%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612459%22%20slang%3D%22en-US%22%3E%3CP%3EI'd%20like%20creat%20a%20vba%20code%20that%20copy%20only%20cells%20that%20not%20contais%200%20to%20it%20a%20did%20that%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3El%20%3D%200%0Al_end%20%3D%20Range(%22G1%22).End(xlDown).Row%0A%0AWhile%20l%20%26lt%3B%20l_end%0A%0A%20%20%20%20Do%20Until%20Range(%22I%22%20%26amp%3B%20l_end).Value%20%26lt%3B%26gt%3B%20%220%22%0A%20%20%20%20%20%20%20%20l_end%20%3D%20l_end%20-%201%0A%20%20%20%20Loop%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20If%20Range(%22I%22%20%26amp%3B%20l_end).Value%20%26lt%3B%26gt%3B%20%220%22%20Then%0A%20%20%20%20%20%20%20%20Range(%22A1%3AV%22%20%26amp%3B%20l_end).Copy%0A%20%20%20%20%20%20%20%20linha%20%3D%20l_end%20%2B%20l%0A%20%20%20%20End%20If%0A%20%20%20%20%0Alinha%20%3D%20l%20%2B%201%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CBR%20%2F%3EhoweverIf%20my%20Table%20only%20have%200%20I%20want%20to%20show%20a%20message%20but%20while%20excute%20this%20part%20of%20code%20l_end%20will%20return%20ranger%20%220%22%20and%20debug%20o%20code%2C%20I%20would%20like%20show%20a%20message%20insteded%20that%20%22The%20form%20is%20blank%22%20.%3CBR%20%2F%3E%3CBR%20%2F%3EDo%20Until%20Range(%22I%22%20%26amp%3B%20l_end).Value%20%26lt%3B%26gt%3B%20%220%22%3CBR%20%2F%3El_end%20%3D%20l_end%20-%201%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20can%20I%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1612459%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612474%22%20slang%3D%22en-US%22%3ERe%3A%20ABOUT%20vba%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612474%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F771260%22%20target%3D%22_blank%22%3E%40Suresapiens%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBefore%20you%20start%20your%20loop%20try%20to%20do%20some%20calculations%20to%20check%20that%20interval%20has%20values%20%26lt%3B%26gt%3B%20of%20Zeros%3C%2FP%3E%3CP%3ESomething%20like%3C%2FP%3E%3CP%3EIf%20application.WorksheetFunction.Sum(Your_Range)%20%3D%200%20then%3C%2FP%3E%3CP%3EMsgbox%20(%22Your%20Custom%20message%22)%26nbsp%3B%3C%2FP%3E%3CP%3EExit%20sub%3C%2FP%3E%3CP%3EElse%3C%2FP%3E%3CP%3E%5BYour%20Loop%5D%3C%2FP%3E%3CP%3EEnd%20if%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612478%22%20slang%3D%22en-US%22%3ERe%3A%20ABOUT%20vba%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612478%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767933%22%20target%3D%22_blank%22%3E%40luthius%3C%2FA%3E%26nbsp%3BTanks%20for%20answer%20But%20I%20need%20to%20know%20the%20number%20of%20last%20line%20completed%20To%20select%20and%20copy.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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
    Loop
        
    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

@Suresapiens 

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

Else

[Your Loop]

End if

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