• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

AutoFill using Rows.Count (VBA)

I wanted to auto fill a column using VBA and back then I was using a particular number in the range like 100-150. so it used to autofill the column till 150 rows and using autofilter I used to delete the additional cells.

Then I came to know about this awesome line -
[Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

Range("E2").Select
Range("E2").AutoFill Destination:=Range("E2:E2")]

This code is working absolutely fine. Now there is only one problem. Suppose if I'm Copying data from Sheet1(Source - RangeA1:D5) and pasting it in Sheet2, writing a vlookup formula in E2 and then using Autofill through above mention code. It will work absolutely fine. However there are instances when the data is only for 2 row(1 Row for Heading and 1 row for the data). So the value of "LR" will become "2" and the code will look like this after updation of the LR value -
[Range("E2").AutoFill Destination:=Range("E2:E2")]

Running this line would show an error like this -
"Run-time error '1004':
Application-defined or object-defined error.

So how to solve this situation when VBA is trying to autofill but there is nothing to autofill.
 
You will have to check the rows and then use an If Statement

So If you have a Header Row 2
and LR = 2
then use

Code:
If LR = 2
  ' Do something for 1 Row
else
  'autofill code
End if


If you can post an actual file we can give a more specific rsponse
 
Code:
Sub AF()

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
   
If LR > 2 Then
  Range("B2").FormulaR1C1 = "=RC[-1]"
  Range("B2").AutoFill Destination:=Range("B2:B" & LR)
End If

End Sub
 
Thanks a lot @Hui for the solution. This is working absolutely fine.

In the meanwhile I got one more code on a website -

Code:
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

Range("B2").FormulaR1C1 = "=RC[-1]"

If Application.CountA(Range("A:A")) = 2 Then GoTo oneresultskip

Range("B2").AutoFill Range("B2:B" & LR) 'Cells(Rows.Count, 1).End(xlUp).Row)
          
oneresultskip:
          
Range("B:B").Value = Range("B:B").Value

End Sub
[CODE]

However, the lines are less in your solution  :)
 
Last edited:
When I want to fill rows with consecutive numbers, I use the following code.

Code:
Sub AutoNumberFill()
    Dim x As Long
    Dim rn As Long
    Dim cn As String, sn As Long
    cn = InputBox("Which Column to fill?")
    rn = InputBox("How many rows to fill?")
    sn = InputBox("Which row to start fill?")

    Application.ScreenUpdating = False
    Application.StatusBar = "Macro Running"
    x = InputBox("What is the starting number?")
    Range(cn & sn).Select
    Do Until ActiveCell.Row = rn + sn
        If ActiveCell.EntireRow.Hidden = False Then
            ActiveCell.Value = x
            x = x + 1
        End If
        ActiveCell.Offset(1).Select
    Loop
    Application.ScreenUpdating = True
    Application.StatusBar = "Completed"
End Sub
 
Back
Top