SOLVED

Count rows, end up will not work with only one row

%3CLINGO-SUB%20id%3D%22lingo-sub-2665904%22%20slang%3D%22en-US%22%3ECount%20rows%2C%20end%20up%20will%20not%20work%20with%20only%20one%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2665904%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20the%20below%20code%20to%20find%20the%20last%20row%2C%20then%20autofill%20column%20A.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ELR%20%3D%20Cells(Rows.Count%2C%204).End(xlUp).Row%3CBR%20%2F%3ERange(%22A2%22).AutoFill%20Destination%3A%3DRange(%22A2%3AA%22%20%26amp%3B%20LR)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20unless%20there%20is%20only%20one%20value%20in%20column%204%20(D2).%20If%20there%20are%20no%20values%20in%20D2%20or%20if%20there%20is%20more%20than%20one%20value%20in%20column%20D%20(D2%3AD3...%20D2%3AD2000)%20the%20formula%20works.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20do%20I%20need%20to%20do%20if%20there%20is%20only%20one%20value%20in%20column%20D%20(D2)%3F%3C%2FP%3E%3CP%3ERow%20A1%3AE1%20are%20column%20heads.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETank%20you%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2665904%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2666052%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20rows%2C%20end%20up%20will%20not%20work%20with%20only%20one%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2666052%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1132462%22%20target%3D%22_blank%22%3E%40starr0650%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3E%20%20%20%20LR%20%3D%20Cells(Rows.Count%2C%204).End(xlUp).Row%0A%20%20%20%20If%20LR%20%26lt%3B%26gt%3B%202%20Then%0A%20%20%20%20%20%20%20%20Range(%22A2%22).AutoFill%20Destination%3A%3DRange(%22A2%3AA%22%20%26amp%3B%20LR)%0A%20%20%20%20End%20If%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2669682%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20rows%2C%20end%20up%20will%20not%20work%20with%20only%20one%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2669682%22%20slang%3D%22en-US%22%3EThanks%20Hand!%20This%20worked%20perfectly.%3C%2FLINGO-BODY%3E
New Contributor

I'm using the below code to find the last row, then autofill column A. 


LR = Cells(Rows.Count, 4).End(xlUp).Row
Range("A2").AutoFill Destination:=Range("A2:A" & LR)

 

It works unless there is only one value in column 4 (D2). If there are no values in D2 or if there is more than one value in column D (D2:D3... D2:D2000) the formula works. 

 

What do I need to do if there is only one value in column D (D2)?

Row A1:E1 are column heads.

 

Tank you for your help!

2 Replies
best response confirmed by starr0650 (New Contributor)
Solution

@starr0650 

For example:

    LR = Cells(Rows.Count, 4).End(xlUp).Row
    If LR <> 2 Then
        Range("A2").AutoFill Destination:=Range("A2:A" & LR)
    End If
Thanks Hand! This worked perfectly.