Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Copy down entries using VBA

Posted on 2013-02-07
5
Medium Priority
?
289 Views
Last Modified: 2013-02-07
Dear Experts:

I have got thousand of item number entries in Column C
These entries are not adjacent to each others. Example:

7
blank cell
blank cell
blank cell
14
blank cell
blank cell
blank cell
blank cell
12
blank cell
blank cell
blank cell
blank cell
5
blank cell
blank cell
44

A macro should now perform the following action:
copy down the first entry it finds in column C until it hits a different no.
After hitting a different no, this new number has to be copied down until it hits another different no.
etc.

RESULT:

7
7
7
7
14
14
14
14
14
12
12
12
12
12
5
5
5
44

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

I have attached a sample file for your convenience.

CopyDownEntries.xlsx
0
Comment
Question by:AndreasHermle
  • 2
  • 2
5 Comments
 
LVL 16

Expert Comment

by:terencino
ID: 38863308
Hi Andreas while the cells look blank they have many spaces in them?
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 total points
ID: 38863310
Hello,

you don't need a macro for that. Select the column and run a Find and Replace to replace all spaces with nothing. In the Find What box enter a space, leave the Replace with box blank and hit "Replace All". Then ...

Select the column
Hit F5 > Special > tick Blanks > OK
Format the selection as "General"
Without changing the selection, type a = and hit the up arrow
Hold down the Ctrl key and hit Enter.

Done.

You can now copy and paste special > values to replace the formulas with the values.

cheers, teylyn
0
 
LVL 16

Assisted Solution

by:terencino
terencino earned 1000 total points
ID: 38863467
And here is a macro to do just that, except it copies the cells including formats as some of the formats in the blank cells are different. The file extension has changed to XLSM now, and it assumes there are no spaces in the item numbers, otherwise they will be gone.
...Terry
Sub FillInTheBlanks()
Dim act As Range, c As Range
Set act = ActiveSheet.UsedRange.Columns(1)
act.Replace What:=" ", Replacement:=""
For Each c In act.SpecialCells(xlCellTypeBlanks)
    c.Offset(-1, 0).Copy c
Next c
End Sub

Open in new window

CopyDownEntries.xlsm
0
 

Author Comment

by:AndreasHermle
ID: 38863477
Hi Teylyn,

this is great stuff. Worked like a charm, this built-in solution! Thank you very much for this superb tip! This solution will get points awarded, of course.

But to be honest with you I also would like to see how a macro solution would look like, as I stated in my initial request. Hope you do not mind.

Regards, Andreas
0
 

Author Closing Comment

by:AndreasHermle
ID: 38864572
Dear all,

great jobs from both of you. A built-in solution as well as a macro solution! Who could ask for more?

Thank you very much for your superb support.

Regards, Andreas
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question