Solved

Copy down entries using VBA

Posted on 2013-02-07
5
242 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
Comment Utility
Hi Andreas while the cells look blank they have many spaces in them?
0
 
LVL 50

Accepted Solution

by:
teylyn earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel and Formulas 8 30
SUM every other column 10 36
Multi vLookup (Excel vba) 12 26
Populate data based ona  criteria 6 17
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now