IF Not ISEmpty Autofill problem

[RaydenUK]

I have a macro that will autofill column C with the contents of "C1" to the last used row from column A. This code works great unless row 1 is the only row. That is why I used "If Not IsEmpty("A2") Then" because I wanted it to recognize that if "A2" is empty then do not autofill. When i use this code on only 1 row then i get the error "1004: Autofil method of range class failed". Can anyone help me out with this code? Thank you.

Sub UsedRange()

    Dim lastrow As Long
    
   If Not IsEmpty("A2") Then
       lastrow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
       Range("C1").AutoFill Range("C1:C" & lastrow), xlFillCopy
   End If


End Sub

 

[V_Malkoti]

Only one cell to select and fill itself; thus the error. Update your Autofill statement as:

If lastrow > 1 Then Range("C1").AutoFill Range("C1:C" & lastrow), xlFillCopy

 

[RaydenUK]

I tried this code but it doesn't do anything as if I never ran the code.

 

[V_Malkoti]

How many rows of data are present in your spreadsheet? What value does lastrow variable show?

 

[RaydenUK]

i actually use this code for several different macros but in one particular macro that gives the debug error there may be 1 row or 200 rows. The row number is random depending the amount of samples that i have for that day.

 

[V_Malkoti]

I tried this code but it doesn't do anything as if I never ran the code.

Are you saying that it doesn't autofill in any data sample set at all, or just few?
I want to know how many rows were there when you saw this behavior. I can't just guess the root cause issue without some information.

 

[RaydenUK]

Lets say there are 3 rows of data. Columns A1:A3 have numbers in them, columns B1:B3 has names, and C1 has a group name. I need the group name in C1 to fill down to C3. With my original code this worked fine. However, if I only had one row present then it would throw a debug message. Now with this code it will not do anything whether there is 2 rows or 10 rows of info, rather it won't fill down like it should. This is fine if there is only 1 row because it does nothing as it should. When i use the lastrow variable in a message box then it always shows "2", no matter how many rows are filled in.

 

[V_Malkoti]

Well the logic you originally had for lastRow variable is intact, so it couldn't have been the code change.
lastRow variable is populated with last row number of active sheet. Is the sheet, on which you are trying to autofill, active when you run this code? Maybe lastRow is getting row number from some other sheet which is active.

 

[RaydenUK]

Yes it is active, however, I may not have used the lastrow variable correctly. How would you suggest doing it. I'm sorry I am still fairly new to coding.

 

[V_Malkoti]

There doesn't seem to be anything wrong with your code. I'm running it (with a slight variation) and it fills down values correctly.

Sub UsedRange()
    Dim lastrow As Long
  
    lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    If lastrow > 1 Then Range("C1").AutoFill Range("C1:C" & lastrow), xlFillCopy
End Sub

Does C1 cell always have a value or is it blank?