VBA AutoFill and Last Row Not Working With Single Row of Data

jdorian1685

New Member
Joined
May 10, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello,

I need to create a code that will autofill data to the last cell in a various columns (the last row will always have the same data in each cell). I have a code that works great for two or more rows of data but it breaks whenever there is only one row of data (when really I don't need to use the autofill code). How do I write something that will skip an autofill command if there is only one row OR alternatively, how do I write a code that will run the autofill but not break? I need something that will work on a few columns at a time not just one. Here's part of what I have so far. In this example, Column C enters a formula and then the autofill drags it down to the last Row in Column C. Column H is not an autofill but a value formatting for an entire column down to the last row.

Thanks in advane!


VBA Code:
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[2]>0, RC[2], """")"
    lastRow = ActiveSheet.UsedRange.Rows.Count
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C" & lastRow)
    Range("C2:C" & lastRow).Select
    Range("H2:H" & lastRow).Value = Range("H2:H" & lastRow).Value
    Range("H2:H" & lastRow).Select
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,772
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Rather than using Autofill, just put the formula into all the cells like
VBA Code:
   lastrow = ActiveSheet.UsedRange.Rows.Count
   Range("C2:C" & lastrow).FormulaR1C1 = "=IF(RC[2]>0, RC[2], """")"
 
Solution

jdorian1685

New Member
Joined
May 10, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Rather than using Autofill, just put the formula into all the cells like
VBA Code:
   lastrow = ActiveSheet.UsedRange.Rows.Count
   Range("C2:C" & lastrow).FormulaR1C1 = "=IF(RC[2]>0, RC[2], """")"

Thank you so much! This is so efficient and a much better way of doing it. I still have a ways to go with my creativity when solving these problems. I appreciate your help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,772
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,143,907
Messages
5,721,440
Members
422,362
Latest member
elliotpat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top