Need Macro to paste values from above in Blank cells

[floridagunner]

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

 

[Greg Truby]

Welcome to the board.

Assuming your cells contain constants & not formulae, this should get you started...

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]

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]

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.

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.

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

 

[floridagunner]

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]

You can manually enter the code tags by simply typing

 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.