Need Macro to paste values from above in Blank cells

floridagunner

Board Regular
Joined
Jul 20, 2007
Messages
60
Hello I am looking for a macro that will take the series of values immediately above the blank cells in a column and copy them down.

Example:

Column A

12345
45678


2365
6524
6983

The cells in column Column A are formatted in a way where if there are 2 Values in cell A1 and A2, there are also two blank values below them. If there are 3 values in A5,A6 and A7 there are three blank cells below that.

What I need is this:

12345
45678
;Macro to add 12345
;Macro to add 45678
2365
6524
6983
;Macro to add 2365
;Macro to add 6524
;Macro to add 6983

Hope I have explained this properly.

Thanks
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
Welcome to the board.

Assuming your cells contain constants & not formulae, this should get you started...
Code:
Sub quickndirty()
    For Each a In Range("a:a").SpecialCells(xlCellTypeConstants, 23).Areas
        With a
            .Copy .Offset(.Rows.Count)
        End With
    Next a
End Sub
Change the "a:a" range addy to correspond to your target column.
 

floridagunner

Board Regular
Joined
Jul 20, 2007
Messages
60
THANKS MAN

Does this mean that If the cells do contain formulas I should copy and paste them as values?

Another thing is there a way to amend the code so that it does not copy the topmost value in the series?

Example:

2365
6524
6983
;Macro to add 6524
;Macro to add 6983

This Macro would only copy down 6524 and 6983 and NOT COPY 2365.

THANKS FOR ALL YOUR HELP
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
OK - here's your situation... I'm one o' them MVP's that don't spoonfeed too much. I tend to "nudge in the right direction" and let folks teach themselves as much as they can. I do realize yer a newbie and I'll be patient. :wink:

Here's a version of the code that will do what you describe. Try comparing it with the code already provided and see if you can suss out the differences.

Code:
Sub quickndirtyV2point0()
    For Each a In Range("a:a").SpecialCells(xlCellTypeConstants, 23).Areas
        With a
            If .Rows.Count > 1 Then
                .Offset(1).Resize(.Rows.Count - 1).Copy .Offset(.Rows.Count).Resize(.Rows.Count - 1)
            End If
        End With
    Next a
End Sub

In the VBE (Visual Basic Editor) your very bestest bestest buddy is the F2 key (Object Explorer). Looking at the code you see that we're using the SpecialCells method w/ the xlCellTypeConstants constant. Try typing either one of those terms into the OE's search box. If you type in SpecialCells you'll see a listing for that method. Select it in the "members" window & hit F1; read up. Do that and you'll see why I asked about formulas versus constants. :biggrin:

Same thing with Offset, Resize and Areas properties for RANGE objects, find 'em in the OE & tap the ol' F1 key.
 

floridagunner

Board Regular
Joined
Jul 20, 2007
Messages
60
Hi Greg thanks for your response. I am looking at your code and will get back to you.

Thanks for your help again.

By the way WHERE IS THE CODE TAG?

SORRY I AM A TOTAL BEGINNER
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
You can manually enter the code tags by simply typing

Code:
 blah blah

Or, if you click the POST REPLY button instead of using the QUICK reply box, you will see buttons for various tags, like the bold tags, font tags, color tags, etc. as well as a limited pallet of emoticons.
 

Forum statistics

Threads
1,181,730
Messages
5,931,716
Members
436,800
Latest member
abowalid98

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