Solved

Copy down entries using VBA

Posted on 2013-02-07
5
284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

628 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