Amit Punia
Member
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.
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.