Macro to copy down text in sections needed

0
votes

I need an Excel macro to
1. Copy a string
2. Find a different string further down the column
3. Paste the copied string to all the cells in between
4. Delete 4 rows between sections.
5. Repeat for the next section until the entire file is completed.

Data looks like this
ABC
(blank)
AM
AM
AM
File(s)
(blank)
DEF
(blank)
AM
AM
AM
AM
AM
AM
File(s)
(blank)
GHI
(blank)
PM
PM
PM
File(s)
(blank)
JKL
(blank)
PM
PM
PM
PM
PM
PM
File(s)

etc. repeated for over a million lines.

I need it to look like

ABC
ABC
ABC
ABC
DEF
DEF
DEF
DEF
DEF
DEF
GHI
GHI
GHI
GHI
JKL
JKL
JKL
JKL
JKL


The number of rows in each section varies.

1
excelexcel-2010vba

1 Answers

4
votes

The code for doing this is pretty straightforward (just a couple of Do...Until loops), but the best way for you to obtain the solution for this is to use the Macro - Record function in Excel.

If you will be using VBA in the future, and this is your first entry into it, there is no better way to learn the basics than to hit the Record Macro button, manually perform some of the steps on the spreadsheet and then look at the code that Excel generated.

You will be able to see the steps that are necessary to perform the operations needed. The only thing that you will need to do then is to wrap those steps in a loop, and do a logic check (If..Then) to check the strings in the cells, as well as keep track of the number of rows that were skipped before encountering the next string (to use in the paste statement).