• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Copy down entries using VBA

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
Andreas Hermle
Asked:
Andreas Hermle
  • 2
  • 2
2 Solutions
 
terencinoCommented:
Hi Andreas while the cells look blank they have many spaces in them?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
terencinoCommented:
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
 
Andreas HermleTeam leaderAuthor Commented:
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
 
Andreas HermleTeam leaderAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now