Solved

Copy down entries using VBA

Posted on 2013-02-07
5
265 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 earned 250 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 250 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

773 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