Quick macro for copying down in a variable range

cyse

New Member
Joined
Jul 7, 2002
Messages
8
Hi, I'm hoping you can help.

I have a long list of cells that I need to fill in the blanks between entries with the contents of the last cell...so if the data looked like this...

Apple


Pear

Orange

I want it to look like this...


Apple
Apple
Apple
Pear
Pear
Orange

I thought the best way to do it was to use a macro with relative references and just repeatedly call the macro...here is what I've created

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/22/2007 by Danette Nguyen
'
' Keyboard Shortcut: Ctrl+q
'
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A9").Select
Selection.FillDown
Selection.End(xlDown).Select
End Sub

...but the cell range A1:A9 is of defined length and my length between each entry is variable. How can I modify this macro to achieve what I want. Optimally, the macro replaces me from the following keystrokes...

starting at the first entry
ctrl-shift-down arrow
shift-up arrow
ctrl-d
ctrl-shift-down arrow
end

Thanks for your help
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Do you really need code?
  1. Select the column with the blanks.
  2. Goto Edit>Goto...Special and select blanks.
  3. Goto the formula bar, enter =A1 (or whatever the first cell with data is) and then press CTRL+ENTER.
  4. Select the column, copy and paste special values.
 

cyse

New Member
Joined
Jul 7, 2002
Messages
8
I need to fill in the blanks because the rest of the columns have data and I am trying to fill in those cells so I can use the information as a database. Without those cells filled in, I won't be able to accomplish creating the database of information.
Thanks
 

eblake

Active Member
Joined
Aug 18, 2004
Messages
258
There is probably a better way to do it by looping through the blanks and using fill down, but I already had something similiar written...

Code:
Sub Filler()
myVar = "OOPS"
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For I = 1 To LastRow
    If Range("A" & I).Value > "" Then
        myVar = Range("A" & I).Value
    Else
        Range("A" & I).Value = myVar
    End If
Next I
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
I need to fill in the blanks because the rest of the columns have data and I am trying to fill in those cells so I can use the information as a database. Without those cells filled in, I won't be able to accomplish creating the database of information.
Thanks
I realise what you want to do, but do you really need to use code.

Note I'm not saying it's not possible with code.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,317
Messages
5,600,915
Members
414,416
Latest member
Nobu

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