Excel VBA Macro to Autofill multiple columns to last row

lildogz96

New Member
Joined
Jun 20, 2019
Messages
3
Hello,
I am new to macros and struggling to get a desired range, two adjacent columns (A7,B7), to autofill to the last row of C. Another problem is that I have some data throughout columns A and B and I do not want to overwrite those. So I think I need a loop to get around that?
Title 1Title 2Title 3
Entry A2Entry B2Entry C2
Entry C3
<strike></strike>
Entry A4
<strike></strike>
<strike></strike>
Entry B4
<strike></strike>
Entry C4
<strike></strike>
Entry C5
<strike></strike>
Entry C6
<strike></strike>
Entry A7
<strike></strike>
<strike></strike>
Entry B7
<strike></strike>
Entry C7
<strike></strike>
Entry C8
<strike></strike>
Entry C9
<strike></strike>
Entry C10

<tbody>
</tbody>
<strike></strike>
My spreadsheet looks something like that and I want to be able to use this macro on other spreadsheets. I'm trying to get it to autofill say A2 and B2 down to C3, but then I need to autofill A4 and B4 to C6 and so on.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Welcome to the Board!

You can actually do this pretty quickly and easily without VBA.
See: https://www.ablebits.com/office-addins-blog/2014/05/02/fill-blanks-excel/

If you want it to be a part of a VBA solution, you can turn on your Macro Recorder and manually perform the steps in the prior link. Then you should have the code to do what you want.
If you need any helping modifying that code after the fact, post it here along with an explanation of what needs to be altered.
 
Upvote 0
Thanks, that worked for this sheet I'm working on, but what if I had a different number of rows? I will be inserting this piece of VBA code into a larger macro that is being used to make an ugly spreadsheet look nice and streamlined so that I can sort and filter out specific items I want.

So here is the code that I have so far:

Code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    Range("A6:B166").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.FormulaR1C1 = "=R[-1]C"[/FONT]

How do I get it to autofill both columns A and B if I'm starting at A6 then down to the last row of B?
 
Upvote 0
Since it looks like you are using column C to determine the last row, you can do this:
Code:
    Dim lr As Long

'   Find last row with data in column C
    lr = Cells(Rows.Count, "C").End(xlUp).Row

'   Populate formulas in blank cells
    Range("A6:B" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Note how I combined your four lines into one line.
The Macro Recorder is very literal, so it records each selection. But it usually isn't necessary to select cells to work with them, and almost anywhere where you have one line of code end in ".Select" and the next start "Selection", those two rows can be combined into one.

It will work just fine either way. Combining them just makes the code a little shorter, and will help your code run faster (selecting cells slows it down).
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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
Back
Top