Excel VBA Macro to Autofill multiple columns to last row

[lildogz96]

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? [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Title 1[/TD]
[TD]Title 2[/TD]
[TD]Title 3[/TD]
[/TR]
[TR]
[TD]Entry A2[/TD]
[TD]Entry B2[/TD]
[TD]Entry C2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]

Entry C3​

[/TD]
[/TR]
[TR]
[TD]<strike></strike>

Entry A4​

<strike></strike>[/TD]
[TD]<strike></strike>

Entry B4​

<strike></strike>[/TD]
[TD]

Entry C4​

[/TD]
[/TR]
[TR]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD]

Entry C5​

[/TD]
[/TR]
[TR]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD]

Entry C6​

[/TD]
[/TR]
[TR]
[TD]<strike></strike>

Entry A7​

<strike></strike>[/TD]
[TD]<strike></strike>

Entry B7​

<strike></strike>[/TD]
[TD]

Entry C7​

[/TD]
[/TR]
[TR]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD]

Entry C8​

[/TD]
[/TR]
[TR]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD]

Entry C9​

[/TD]
[/TR]
[TR]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD]

Entry C10​

[/TD]
[/TR]
</tbody>[/TABLE]
<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.

 

[Joe4]

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.

 

[lildogz96]

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:

[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?

 

[Joe4]

Since it looks like you are using column C to determine the last row, you can do this:

    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).

 

[lildogz96]

Thank you! That was a huge help

 

[Joe4]

You are welcome!